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

Community · Accepted Answer · 2017-05-23 12:29:29Z

3

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Sep 5, 2014 at 16:57

Augustin Riedinger

22.4k33 gold badges150 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL functions: return setof tablename as variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related