0

I have this stored procedure

CREATE OR REPLACE FUNCTION internal_tcell.format_test()
    RETURNS TABLE
            (
                id    text,
                name  text,
                count integer
            )
AS $$
DECLARE
    schema RECORD;
BEGIN
    FOR schema IN EXECUTE
        format(
                'SELECT schema_name FROM information_schema.schemata WHERE schema_name like %L',
                'my_schema_%')
        LOOP
            RETURN QUERY EXECUTE
                format('select t.id, t.name, t.count ' ||
                       'FROM %I.my_table t ',
                       schema.schema_name);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

The output of this looks like

+--------------------------------------------------+
| format_test                                      |
|--------------------------------------------------|
| (id1,name1,1)                                    |
| (id2,name2,4)                                    |
| (id3,name3,1)                                    |
...

How do I change this function to return the output formatted/annotated by column, like a normal select:

+----------------------+--------------------+----------+
| id                   | name               | count    |
|----------------------+--------------------+----------|
| id2                  |  40.0              | 4        |
...

Also wondering if it is possible to order by one of the output columns, or if having the for look precludes that.

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
marathon
  • 7,162
  • 15
  • 65
  • 124

0 Answers0