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

Question

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.

Of course, the same example could be implemented in one single function, but I would still find the same problem about a function returning table type executing different queries that return record type. Thank you in advance.

user330315 · Accepted Answer · 2020-05-02 10:54:33Z

2

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;

answered May 2, 2020 at 10:54

user330315

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

3 Comments

Atreide Over a year ago

Thank you very much indeed. It was big help.

Atreide Over a year ago

Thank you very much for such a concise and quick answer. I was really misunderstanding the fact you mention and thinking that the RETURN QUERY would be giving control back to the calling function. Good for Postgre, having such an open sintaxis to do things.

Atreide Over a year ago

Concerning your comment, well, actually tables don´t have common fields, what I want to get about the schema tables does have an homogeneous structure, it is about some counts concerning relations between data in different tables, obtained from a relation coming from the schema tables, but with some added information, but your answer feats just right for the requirement, so, concerning the second part of your answer, well I take it as really interesting matter to study, this JSON matter, to be able to return a table containing rows with heterogeneous structure, I understand. Really grateful

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related