Create view from the user defined function : Create View « View « PostgreSQL






  1. PostgreSQL
  2. View
  3. Create View

Create view from the user defined function


postgres=#
postgres=# CREATE TABLE myTable (
postgres(#          id int,
postgres(#          sid int,
postgres(#          name text);
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into myTable values(1,2,'a');
INSERT 0 1
postgres=# insert into myTable values(2,3,'b');
INSERT 0 1
postgres=#
postgres=# select * from myTable;
 id | sid | name
----+-----+------
  1 |   2 | a
  2 |   3 | b
(2 rows)

postgres=#
postgres=#
postgres=#
postgres=# CREATE FUNCTION getData(int) RETURNS SETOF myTable AS $$
postgres$#    SELECT * FROM myTable WHERE id = $1;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=#
postgres=# CREATE VIEW vw_GetData AS SELECT * FROM getData(1);
CREATE VIEW
postgres=#
postgres=# SELECT * FROM vw_GetData;
 id | sid | name
----+-----+------
  1 |   2 | a
(1 row)

postgres=#
postgres=# drop function getData(int) cascade;
NOTICE:  drop cascades to rule _RETURN on view vw_getdata
NOTICE:  drop cascades to view vw_getdata
DROP FUNCTION
postgres=# drop view vw_GetData cascade;
ERROR:  view "vw_getdata" does not exist
postgres=# drop table myTable cascade;
DROP TABLE
postgres=#
postgres=#








Related examples in the same category

1.Creating a view with a natural join statement
2.Creates a view of all publishers whose names begin with H