CODEWITHSUNDEEP
postgresql
Atreide
Atreide

Reputation: 267

Postgresql how to implement function returning table invoking function returning record of the same type

I do want to query specific information from all tables in the DB: 

select * 
from [pg_catalog.]pg_tables 
where schemaname = '<Schema>';

The only way I appear to do it (I accept any suggestions) without creating a temporary table, would be to develop a function executing a loop on the schema tables (Loop on Cursor with that query) and then for each table in the schema I would call a function executing dynamic pgsql to obtain the desired information from that table in the schema. This second function requires dynamic sql (I believe) as the table name on which I want to execute a query on, would vary and so, should be passed as an argument to the function.

The idea would be that this "called" function would be returning a record type and then the main (or "calling") function should return a table type, and this is where I get lost.

What I don´t know how to solve (And don´t know that can be solved at all) would be how to construct a function returning a table type (Or some data structure that could be listed when I call the function from sql editor) which internally calls a function returning a record (Same structure of the table returned by "calling" function). In all examples I see about function returning tables, the return is the execution of a select, but I don´t find any example of pgsql function returning table type that internally constructs the table structure. And I don´t know that is possible at all either.

Upvotes: 0

Views: 844

Answers (1)

user330315
user330315

Reputation:

Assuming that all tables have an identical structure, you don't need another function, this can all be done in a single function. 

create function get_all()
  returns table (table_name text, column_1 .., column_2 ..., column_3 ...) -- specify columns and data types
as
$$
declare 
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname = 'bla'
  loop
    l_sql := format('select %L, column_1, column_2, column_3 from %I.%I', 
                    l_rec.tablename, l_rec.schemaname, l_rec.tablename);
    return query execute l_sql;
  end loop;
end
$$
language plpgsql;

As documented in the manual, RETURN QUERY doesn't terminate the function, it only adds another set of rows to the result

If the tables don't have common columns (which makes the question quite strange actually), I would return the rows as JSON values:

create function get_all()
  returns table (table_name text, row_data jsonb) -- specify columns and data types
as
$$
declare 
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname = 'bla'
  loop
    l_sql := format('select %L, to_jsonb(t) from %I.%I as t', 
                    l_rec.schemaname, l_rec.schemaname, l_rec.tablename);
    return query execute l_sql;
  end loop;
end
$$
language plpgsql;

Upvotes: 2

Related Questions