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
.
Best Answer
You cannot use a parameter name (
_t
) as return type.Use polymorphic types instead:
And use
RETURN QUERY EXECUTE
to actually return the results.Call (important!):
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