PostgreSQL functions: return setof tablename as variable

Question

I have this function:

CREATE OR REPLACE FUNCTION remote (_table_name text)
  RETURNS SETOF my_table AS
$func$
DECLARE
   sql text := format('SELECT * FROM %s WHERE id = 1', _table_name);
BEGIN
   RETURN QUERY EXECUTE sql
   USING _table_name;
END
$func$ LANGUAGE plpgsql;

select * from remote('my_table')

But I would like to have a dynamic RETURNS SETOF _table_name.

I get this error if I do so:

ERROR:  type "_table_name" does not exist

How can I achieve that?

Many thanks

score 3 · Accepted Answer · edited May 23 '17 at 12:29

Found the answer here: Refactor a PL/pgSQL function to return the output of various SELECT queries

CREATE OR REPLACE FUNCTION get_remote(_table_name anyelement)
  RETURNS SETOF anyelement AS
$func$
DECLARE
   sql text := format('SELECT * FROM %s WHERE id = 1', pg_typeof(_table_name));
BEGIN
   RETURN QUERY EXECUTE sql
   USING _table_name;
END
$func$ LANGUAGE plpgsql;

select * from get_remote(NULL::my_table)

That was a tricky one though ...

PostgreSQL functions: return setof tablename as variable

1 Answers1