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.