Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
        FROM '|| _t || 
        'WHERE vrs_start_time <= '|| _d ||
        'ORDER BY gid, vrs_start_time DESC';
LANGUAGE plpgsql;

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

share|improve this question
Exact error message? And what is _t? Is there actually a type or table named _t ? – Craig Ringer Apr 19 '14 at 0:37
up vote 3 down vote accepted

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
       SELECT DISTINCT ON (gid) * 
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
   USING _d;
$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

share|improve this answer
Worked like a charm! Thank you very much. Your answers are always both useful as educative. – Alexandre Neto Apr 19 '14 at 18:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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