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

Question

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).

See return next in the documentation.

greg
– greg

2013-05-01 11:16:36 +00:00
Commented May 1, 2013 at 11:16 — greg
– greg, Commented May 1, 2013 at 11:16
@greg That is for plpgsql

Clodoaldo Neto
– Clodoaldo Neto

2013-05-01 11:40:49 +00:00
Commented May 1, 2013 at 11:40 — Clodoaldo Neto
– Clodoaldo Neto, Commented May 1, 2013 at 11:40

Clodoaldo Neto · Accepted Answer · 2013-05-01 11:38:56Z

1

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

edited May 1, 2013 at 11:38

answered May 1, 2013 at 11:33

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

greg · Accepted Answer · 2013-05-01 12:24:18Z

0

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 │
└───────┘

answered May 1, 2013 at 12:24

greg

3,5351 gold badge27 silver badges35 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related