returning a query result from dynamic query

Question

reading about how to make an SP that returns the results of a query it seems I must do this kind of thing (from tutorial site)

CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;                                                     -- Declare a cursor variable
    BEGIN
      OPEN ref FOR SELECT city, state FROM cities;   -- Open a cursor
      RETURN ref;                                                       -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;

OK, I get this, but I want to pass the SQL in as a paramter so I need to do (I think)

EXECUTE mysql ......

But I dont see how to make EXECUTE return a cursor

EDIT: OK now I see that I misunderstood what the non dynamic case does. I expected to be able to do select show_cities() and have it do that same thing as SELECT city, state FROM cities, it does not. Of course now that I think about it this isnt surprising. I want to return the actual set of records.

Possible duplicate of [Dynamically generated CURSOR in Postgresql](https://stackoverflow.com/questions/34495593/dynamically-generated-cursor-in-postgresql) — JGH, May 10 '18 at 17:28
https://stackoverflow.com/a/11751557/5315974 look the "Various complete table types" section... — Vao Tsun, May 11 '18 at 07:37

score 0 · Accepted Answer · answered May 11 '18 at 07:43

In your case smth like:

t=# CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  true /*or some filter in additional arguments or so */
      ORDER  BY true /*or some filter in additional arguments or so */'
    , pg_typeof(_tbl_type))
   ;
END
$func$ LANGUAGE plpgsql;
CREATE FUNCTION

would work:

t=# create table city(i serial, cn text);
CREATE TABLE
t=# insert into city(cn) values ('Moscow'), ('Barcelona'),('Visaginas');
INSERT 0 3
t=# SELECT * FROM data_of(NULL::city);
 i |    cn
---+-----------
 1 | Moscow
 2 | Barcelona
 3 | Visaginas
(3 rows)

all credits to Erwin with his https://stackoverflow.com/a/11751557/5315974 which is one obligatory reading

returning a query result from dynamic query

1 Answers1