Convert into PostgreSQL Dynamic Query

Question

Below is one function which has one query ,

Now I want to convert into dynamic query. I want one table name parameter so query return data from multiple tables.

please help me in this I am new in PostgreSQL , Thanks in Advance !

create or replace function GetEmployees() 
returns setof weather as 
'select * from weather;' 
language 'sql';

You should always refer to the documentation. Trust me, it's a programmer's best friend. postgresql.org/docs/current/static/sql-createfunction.html — Telmo Marques
– Telmo Marques, Commented Jun 5, 2013 at 12:58
Anu, is this a follow-up to stackoverflow.com/questions/16937848/… ? — Craig Ringer
– Craig Ringer, Commented Jun 5, 2013 at 13:00
If you want to pass the table name, you can't easily make this a "returns setof" function as the the passed table name might have a totally different structure than the weather table. — user330315
– user330315, Commented Jun 5, 2013 at 13:03

Community · Accepted Answer · 2017-05-23 12:28:57Z

This is basic PL/PgSQL. Use PL/PgSQL's EXECUTE .. USING statement and format function with the %I format-specifier.

create or replace function get_sometable(tablename regclass) 
returns setof whatever_type as 
BEGIN
RETURN QUERY EXECUTE format('select * from %I";', tablename);
END;
language 'plpgsql';

This will only work if all tablenames you might pass return compatible result types, as occurs with partitioning. Otherwise you'll have to return SETOF RECORD and then pass the table layout in the function invocation. See the documentation for a discussion of RECORD and SETOF RECORD.

Look into RETURNS TABLE as another convenient alternative for when the table types aren't compatible but you can still return a compatible subset via a suitable SELECT list.

If you're doing table partitioning, you should really do it as the PostgreSQL documentation on table partitioning advises, using table inheritance and triggers.

(Elaborating on Convert SQL Server stored procedure into PostgreSQL stored procedure)

Collectives™ on Stack Overflow

Convert into PostgreSQL Dynamic Query

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