3

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as arguments and returns a set of the same table.

The query I need is a bit more complicated, but for my question the following example should be enough.

This is how I'm trying to create the function:

CREATE OR REPLACE FUNCTION "vsr_versioning_at_time"(_t regclass
                                                  , _d timestamp without time zone)
RETURNS SETOF _t AS
$$
BEGIN
    EXECUTE 'SELECT DISTINCT ON (gid) * 
        FROM '|| _t || 
        'WHERE vrs_start_time <= '|| _d ||
        'ORDER BY gid, vrs_start_time DESC';
END
$$
LANGUAGE plpgsql;

The creation of this function fails since _t is not recognized by the RETURN SETOF _t.

1
  • Exact error message? And what is _t? Is there actually a type or table named _t ? Apr 19, 2014 at 0:37

1 Answer 1

3

You cannot use a parameter name (_t) as return type.
Use polymorphic types instead:

CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
       SELECT DISTINCT ON (gid) * 
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
     )
   USING _d;
END
$func$  LANGUAGE plpgsql;

And use RETURN QUERY EXECUTE to actually return the results.
Call (important!):

SELECT * FROM vsr_versioning_at_time(NULL::mytable, '2014-04-18 12:00');

A lot more details in this related answer on SO (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries

1
  • 1
    Worked like a charm! Thank you very much. Your answers are always both useful as educative. Apr 19, 2014 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.