Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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

1 Answer 1

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
$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

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

Your Answer

 
discard

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.