title: Add schema name to dynamic Postgres query
body: <p>My database is using Postgres schemas to provide a separated, multi-tenant environment for users. Every schema has a copy of the same tables.</p>
  
  <p>I have one particular query where I need to join across the schemas, return a list of records (in this case, <code>children</code>). 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.</p>
  
  <p><strong>Current dynamic query</strong></p>
  
  <p><em>(Schema's look like: <code>OPERATOR_SCHEMA_my-great-company</code>)</em></p>
  
  <pre><code>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();
  </code></pre>
  
  <p>This returns something like:</p>
  
  <pre><code>-------------
  | id | name |
  | 1  | Bob  |
  | 2  | Joe  |
  -------------
  </code></pre>
  
  <p>Whereas I 'd like it to return something like: </p>
  
  <pre><code>-------------------------------
  | id | name | schema_name     |
  | 1  | Bob  | darcy's-store   |
  | 2  | Joe  | bob's-4th-store |
  -------------------------------
  </code></pre>
  
  <p>It should be noted that the schema names are user defined, and can have quotes in them.</p>
  
  <p><strong>How can I add in the relevant schema name for each child?</strong></p>
  
  <p>I have tried a few variations of the following:</p>
  
  <pre><code>LOOP
     RETURN QUERY EXECUTE
       format('SELECT %s AS schema_name, * FROM %1$I.children', schema.schema_name);
  END LOOP;
  </code></pre>
  
  <p>But I'm having some issues with formatting etc. There's probably some <code>quote_X</code> functionality I should be using here.</p>
  
  <p>I'm not very knowledgable on Postgres (and databases in general) so your patience is appreciated! </p>
  
  <p><strong>UPDATES</strong></p>
  
  <p>The following are the exact errors I am getting for a few variations.</p>
  
  <pre><code>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.
  </code></pre>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>I'm getting closer, but not quite there yet.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Why is the the return type coming back as unknown? I expected it would be inserting a string and just returning that type.</p>