Add schema name to dynamic Postgres query

Question

My database is using Postgres schemas to provide a separated, multi-tenant environment for users. Every schema has a copy of the same tables.

I have one particular query where I need to join across the schemas, return a list of records (in this case, children). I have this working via a dynamic SQL query, shown below. However, I want to add in a column to each result that specifies the name of the schema that row came from.

Current dynamic query

(Schema's look like: OPERATOR_SCHEMA_my-great-company)

CREATE OR REPLACE FUNCTION all_children_dynamic() RETURNS SETOF children AS $$
DECLARE
  schema RECORD;
BEGIN
  FOR schema IN EXECUTE
    format(
      'SELECT schema_name FROM information_schema.schemata WHERE left(schema_name, 16) = %L',
      'OPERATOR_SCHEMA_'
    )
  LOOP
    RETURN QUERY EXECUTE
      format('SELECT * FROM %I.children', schema.schema_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

--------

-- Usage: SELECT "id", "name" FROM all_children_dynamic();

This returns something like:

-------------
| id | name |
| 1  | Bob  |
| 2  | Joe  |
-------------

Whereas I 'd like it to return something like:

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

It should be noted that the schema names are user defined, and can have quotes in them.

How can I add in the relevant schema name for each child?

I have tried a few variations of the following:

LOOP
   RETURN QUERY EXECUTE
     format('SELECT %s AS schema_name, * FROM %1$I.children', schema.schema_name);
END LOOP;

But I'm having some issues with formatting etc. There's probably some quote_X functionality I should be using here.

I'm not very knowledgable on Postgres (and databases in general) so your patience is appreciated!

UPDATES

The following are the exact errors I am getting for a few variations.

Input:
format('SELECT %s AS schema_name, * FROM %1$I.children', schema.schema_name);

ERROR:  column "operator_schema_don" does not exist
LINE 1: SELECT OPERATOR_SCHEMA_don-t-display-data AS schema_name, * ...
QUERY:  SELECT OPERATOR_SCHEMA_don-t-display-data AS schema_name, * FROM "OPERATOR_SCHEMA_don-t-display-data".children

Input:
format('SELECT %s AS schema_name, * FROM %I.children', quote_literal(schema.schema_name), schema.schema_name);

ERROR:  structure of query does not match function result type
DETAIL:  Returned type unknown does not match expected type uuid in column 1.

UPDATE 2

I'm getting closer, but not quite there yet.

CREATE OR REPLACE FUNCTION all_children_dynamic() RETURNS TABLE (id uuid, schema_name varchar) AS $$
DECLARE
  schema RECORD;
BEGIN
  FOR schema IN EXECUTE
    format(
      'SELECT schema_name FROM information_schema.schemata WHERE left(schema_name, 16) = %L',
      'OPERATOR_SCHEMA_'
    )
  LOOP
    RETURN QUERY EXECUTE
      format('SELECT id, %L AS schema_name FROM %I.children', quote_literal(schema.schema_name), schema.schema_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

ERROR:  structure of query does not match function result type
DETAIL:  Returned type unknown does not match expected type character varying in column 2.
CONTEXT:  PL/pgSQL function all_children_dynamic() line 11 at RETURN QUERY

Why is the the return type coming back as unknown? I expected it would be inserting a string and just returning that type.

Answer 1

Since you are adding a new column (in this case schema name) to your query, the function result is not a setof children. So a table type needs to be returned with the additional column included in it. The syntax can be seen here .

Something like this..

CREATE OR REPLACE FUNCTION all_children_dynamic() RETURNS 
TABLE(col1 col1type,col2 col2type,...., schema_name varchar) AS $$

The second error was due to improper casting as postgres since version 9 has been real specific about return data types. For example. if you are returning varchar(8) in your function return type, you have to return varchar of same length. Hence the casting is required.

Answer 2

-- DROP FUNCTION IF EXISTS padrao.func_create_schema_and_table(text);

CREATE OR REPLACE FUNCTION padrao.func_create_schema_and_table(
    sufixo_schema text)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
    sql_cod text;
begin  
    --STRING QUE ARMAZENA O CÓDIGO QUE SER�? EXECUTADO
    sql_cod = '
    
----------------------------------- CRIA�?O SCHEMA ----------------------------------------  
    
CREATE SCHEMA IF NOT EXISTS schema_'|| sufixo_schema ||' AUTHORIZATION pitjndjzwxxx;
    
---------------------------------- CRIA�?O DAS TABELAS ------------------------------------

CREATE TABLE IF NOT EXISTS schema_'|| sufixo_schema ||'.produtos (
    "pk_id_produto" serial NOT NULL,
    "fk_software_user" serial NOT NULL,
    "produto_codigo" varchar(10) NOT NULL UNIQUE,
    "produto_descricao" varchar(255) NOT NULL,
    "produto_atualizado" varchar(20) NOT NULL,
    "produto_situacao" varchar(20),
    "produto_dados" jsonb,
    PRIMARY KEY ("pk_id_produto"),
    UNIQUE (produto_codigo,fk_software_user)
); 
/*----------------------------------------------------------------------------------------------*/  
    ';
    
    --EM CASO DE ERRO SER�? RETORNADO A MENSAGEM ABAIXO
    raise notice '### ERRO NA EXECU�?O DA FUN�?O POSTGRES ###';
    
    --EXECUÇ�?O DO CÓDIGO QUE FOI SALVO NA VARI�?VEL:sql_cod 
    EXECUTE format(sql_cod, sufixo_schema);

    return true; 
          
END;
$BODY$;

ALTER FUNCTION padrao.func_create_schema_and_table(text)
    OWNER TO pitjndjzwqrxxx;