sql - Add schema name to dynamic Postgres query -

my database using postgres schemas provide separated, multi-tenant environment users. every schema has copy of same tables.

i have 1 particular query need join across schemas, return list of records (in case, children). have working via dynamic sql query, shown below. however, want add in column each result specifies name of schema row came from.

current dynamic query

(schema's like: operator_schema_my-great-company)

create or replace function all_children_dynamic() returns setof children $$ declare   schema record; begin   schema in execute     format(       'select schema_name information_schema.schemata left(schema_name, 16) = %l',       'operator_schema_'     )   loop     return query execute       format('select * %i.children', schema.schema_name);   end loop; end; $$ language plpgsql;  --------  -- usage: select "id", "name" all_children_dynamic(); 

this returns like:

------------- | id | name | | 1  | bob  | | 2  | joe  | ------------- 

whereas 'd return like:

------------------------------- | id | name | schema_name     | | 1  | bob  | darcy's-store   | | 2  | joe  | bob's-4th-store | ------------------------------- 

it should noted schema names user defined, , can have quotes in them.

how can add in relevant schema name each child?

i have tried few variations of following:

loop    return query execute      format('select %s schema_name, * %1$i.children', schema.schema_name); end loop; 

but i'm having issues formatting etc. there's quote_x functionality should using here.

i'm not knowledgable on postgres (and databases in general) patience appreciated!

updates

the following exact errors getting few variations.

input: format('select %s schema_name, * %1$i.children', schema.schema_name);  error:  column "operator_schema_don" not exist line 1: select operator_schema_don-t-display-data schema_name, * ... query:  select operator_schema_don-t-display-data schema_name, * "operator_schema_don-t-display-data".children  input: format('select %s schema_name, * %i.children', quote_literal(schema.schema_name), schema.schema_name);  error:  structure of query not match function result type detail:  returned type unknown not match expected type uuid in column 1. 

update 2

i'm getting closer, not quite there yet.

create or replace function all_children_dynamic() returns table (id uuid, schema_name varchar) $$ declare   schema record; begin   schema in execute     format(       'select schema_name information_schema.schemata left(schema_name, 16) = %l',       'operator_schema_'     )   loop     return query execute       format('select id, %l schema_name %i.children', quote_literal(schema.schema_name), schema.schema_name);   end loop; end; $$ language plpgsql;  error:  structure of query not match function result type detail:  returned type unknown not match expected type character varying in column 2. context:  pl/pgsql function all_children_dynamic() line 11 @ return query 

why the return type coming unknown? expected inserting string , returning type.

since adding new column (in case schema name) query, function result not setof children. table type needs returned additional column included in it. syntax can seen here .

something this..

create or replace function all_children_dynamic() returns  table(col1 col1type,col2 col2type,...., schema_name varchar) $$ 

the second error due improper casting postgres since version 9 has been real specific return data types. example. if returning varchar(8) in function return type, have return varchar of same length. hence casting required.