'Dynamic SQL and Setof Functions in PG

I am researching if it is possible to dynamically create a view and then return all data from it, all in a single function. My goal is to create a single function that will return all data from a dynamically created view, upon execution of the function. I can do it in Oracle and SQL Server, but so far I am not sure if it is that simple in Postgres. If someone could show code samples, ideas or point me in the right direction, I would be super happy :)

I already know how to create a SETOF function or a dynamic SQL function, but getting both to work in a single program/function function is a challenge that I cannot overcome so far ...

postgresqldynamic-sqlpg


Solution 1:[1]

The returned content can be dynamic, the structure should be static.

CREATE OR REPLACE FUNCTION fx(OUT a int, OUT b int)
RETURNS SETOF record AS $$
BEGIN
  RETURN QUERY EXECUTE format('SELECT i, i+1 FROM generate_series(1,3)');
END;

PostgreSQL documentation is good, please, don't be afraid to read it. The plpgsql related pages need no more than two hours.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Arnaud Leymet

Related Questions