Using the result set returned from the function : Function Return « Store Procedure Function « PostgreSQL






Using the result set returned from the function


postgres=#
postgres=# CREATE TABLE myTable (id   int,
postgres(#                       sid  int,
postgres(#                       name text);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES (1, 1, 'Joe');
INSERT 0 1
postgres=# INSERT INTO myTable VALUES (1, 2, 'Ed');
INSERT 0 1
postgres=# INSERT INTO myTable VALUES (2, 1, 'Mary');
INSERT 0 1
postgres=#
postgres=# CREATE FUNCTION getData(int) RETURNS myTable AS $$
postgres$#    SELECT * FROM myTable WHERE id = $1;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT *, upper(name) FROM getData(1) AS t1;
          REATE
 id | sid | name | upper
----+-----+------+-------
  1 |   1 | Joe  | JOE
(1 row)

postgres=#
postgres=# drop function getData(int);
DROP FUNCTION
postgres=# drop table myTable;
DROP TABLE
postgres=#
postgres=#
           
       








Related examples in the same category

1.Define function to add two parameters together
2.Return entire row
3.Returning a concatenated string
4.Return 'double' from function
5.A SQL function that returns a book title based on the ID number passed to the function
6.Return a table from a function