plpgsql function: Return rows from a view created from random table

Question

I want to create a function that returns rows from a view created from a unknown table(s):

    CREATE OR REPLACE FUNCTION tt_query(text, timestamp without time zone)
      RETURNS SETOF record AS
    $$
    DECLARE

    orig_name ALIAS FOR $1;
    data_tt ALIAS FOR $2;

    BEGIN

    [...]

    EXECUTE 'create OR REPLACE TEMP view temp as 
    select * 
    from  '
    ||orig_name 
    ||' where trigger_changed >'
    ||quote_literal(data_tt)
    ||' ORDER BY trigger_changed DESC';

    [...]--other work on view temp

    --NOW I WANT RETURN THE ROW OF view temp
    END;
    $$
    LANGUAGE plpgsql VOLATILE

ok I have think (with yours help) this:

Table:

create table t(a integer, b text);

Function:

CREATE OR REPLACE FUNCTION f()
  RETURNS SETOF record AS
$$
BEGIN

RETURN QUERY EXECUTE 'SELECT * FROM t';

END;
  $$
  LANGUAGE plpgsql VOLATILE

type:

CREATE TYPE y AS (
    a int,
    b text
);

and now is possible?:

select * from f() as y;

y name in my case is a variable an I create it in an other function

When I do select tt_query(val1, val2) I have: ERRORE: la funzione che restituisce insiemi è chiamata in un contesto che non può accettare un insieme SQL state: 0A000 — Michele M
– Michele M, Commented Feb 14, 2014 at 12:14
Run SET lc_messages = 'C' in your session to get default English messages for the duration of your session. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 14, 2014 at 13:13
sorry: ERROR: set-valued function called in context that cannot accept a set — Michele M
– Michele M, Commented Feb 14, 2014 at 13:22
It works if i write: select * from tt_query(val1, val2) t(int a, int c, ... etc); but I want write my query in this mode :-( — Michele M
– Michele M, Commented Feb 14, 2014 at 13:24

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

It could work like this:

CREATE OR REPLACE FUNCTION tt_query(orig_name regclass, data_tt timestamp)
  RETURNS SETOF record AS
$func$
BEGIN

EXECUTE 'CREATE OR REPLACE TEMP VIEW tmp as 
select * 
from  '
|| orig_name 
|| ' where trigger_changed >'
|| quote_literal(data_tt)
|| ' ORDER BY trigger_changed DESC';

-- other work on view tmp

-- return the rows of view temp
RETURN QUERY
SELECT * FROM tmp;

END
$func$  LANGUAGE plpgsql;

Note the use of the object identifier type regclass to automatically avoid SQL injection.
Do not use the outdated syntax var ALIAS for $1 if you don't have to. Declare parameter names instead.
I wouldn't use the keyword temp as identifier, even if that is allowed. Using tmp instead.
Use RETURN QUERY to return a set of records. This can even be a static call without EXECUTE. However, you are returning anonymous records and Postgres demands a column definition list with every call:

SELECT * FROM tt_query('tbl_name', '2014-02-15 12:00')
AS f(col1 int, col2 text, ...);

This is rather unwieldy.

Better solutions

If you know the return type (even if table names are changing, there list of columns may share the same types), declare it at creation time. Consider this related question:
PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

If the return type varies with the the provided table name, there is still a much better solution. Since you are creating a view with SELECT * FROM tbl, you can utilize the well-known type of the table itself as polymorphic parameter:

CREATE OR REPLACE FUNCTION tt_query(orig_name anyelement, data_tt timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN

EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS
   SELECT * FROM  %s
   WHERE  trigger_changed > %L
   ORDER  BY trigger_changed DESC'
  ,pg_typeof(orig_name)
  ,data_tt);

-- other work on view tmp

-- return the rows of view tmp
RETURN QUERY
SELECT * FROM tmp;

END
$func$  LANGUAGE plpgsql;

Simplified Call:

SELECT * FROM tt_query(NULL::tbl_name, '2014-02-15 12:00');

Also using format() for safe & simple string concatenation.

More details in this related answer:
Refactor a PL/pgSQL function to return the output of various SELECT queries

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 14, 2014 at 13:53

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Erwin Brandstetter Over a year ago

@user3297525: Well, good luck! And be sure to report back if you find something. I am afraid you'll find that this is as simple as it gets for varying tables with varying columns.

Pocketsand Over a year ago

@ErwinBrandstetter Please can you advise if I am safe to assume that the temp view will only exist within the BEGIN END block and will not race with other calls of the same function? The docs say 'per session' but I am not 100% sure. Thanks.

Erwin Brandstetter Over a year ago

@Pocketsand: Trust the docs. Temporary views are automatically dropped at the end of the current session, not transaction. So that may very well cause problems for multiple calls in the same session.

Collectives™ on Stack Overflow

plpgsql function: Return rows from a view created from random table

1 Answer 1

Better solutions

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Better solutions

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related