CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresstored-functions
sorin
sorin

Reputation: 170778

How to write a postgres function that returns a set of rows?

Mainly I am looking for implementing some kind of stored views/reports but with parameters so you can easily update them.

I tried to folow the instructions from http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions but so far I wasn't able to get a list of results back.

CREATE OR REPLACE FUNCTION myreport(in TEXT) returns setof TEXT AS
$$

   select * from ... where ... = '$1'

$$
language sql;

select * from myreport('XOP');

While the internal SQL works well and returns the desired results, it seems that the function execution returns only one item, instead of a list of rows (in my case the select returns a single text column).

Upvotes: 1

Views: 142

Answers (2)

greg
greg

Reputation: 3505

You can return several lines using the TABLE return type:

CREATE FUNCTION plop() RETURNS TABLE (value int) AS $sql$
    SELECT generate_series(1, 10) AS value;
$sql$ LANGUAGE sql;

Then 

SELECT * FROM plop();
┌───────┐
│ value │
├───────┤
│     1 │
│     2 │
│     3 │
   ...
│    10 │
└───────┘

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

Take the quotes out from the parameter:

select * from ... where ... = $1

But that returns all columns in instead of a single text column as declared in the function definition (setof text). It should be:

select text_col from ... where ... = $1

Or if you want all columns declare it as returning a setof the_table:

CREATE OR REPLACE FUNCTION myreport(in TEXT) returns setof the_table AS

Upvotes: 1

Related Questions