Conditonally create temporary table Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a SELECT over multiple schemas. Can I approach this in another way?

CREATE OR REPLACE FUNCTION table_exists(v_table text)
  RETURNS boolean AS
$BODY$
  DECLARE
    v_count int;
    v_sql text;
BEGIN
  v_sql = 
    'SELECT ' ||
    '  count(1) ' ||
    'FROM ' ||
    '  information_schema.tables ' ||
    'WHERE ' ||
    E'  table_name=\'' || v_table || E'\'';

  EXECUTE v_sql INTO v_count;
  RETURN v_count&gt;0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
  COST 100;

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT distinct table_schema FROM information_schema.tables WHERE table_schema NOT IN('information_schema', 'pg_catalog', 'public') AND table_type='BASE TABLE'
  LOOP
  IF table_exists('combined_settings') THEN
    EXECUTE '
    INSERT INTO combined_settings
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''  
    ';

  ELSE
    EXECUTE '
    CREATE TEMP TABLE combined_settings AS
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''
    ';
  END IF;

  END LOOP;
  EXECUTE '
  COPY (SELECT * FROM combined_settings) TO ''/tmp/out-' || current_database() || ''' CSV;
  DROP TABLE IF EXISTS combined_settings;
  ';
END$$;
You do not need a function to check if the temporary table exists. You do not even need the table. The query below gives all tables settings in all schemas (except of public and system schemas) in a database, with appropriate schema names:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE 
    table_type = 'BASE TABLE' 
    AND table_name = 'settings'
    AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')


In the anonymous function below we shall build sql query instead of storing results in a temp table, according to the following pseudocode:

for all tables 'settings':
    build select query text and store it in 'vselect',
    append 'vselect' to 'vquery' joining consecutive selects with word 'UNION'


At the end of the loop we shall have in vquery something like this:

SELECT 'schema1', prefix, id, value FROM schema1.settings WHERE id = 'VERSION'
UNION
SELECT 'schema2', prefix, id, value FROM schema2.settings WHERE id = 'VERSION'
-- etc


and we are going to use this in COPY to a file.  

The function:    

DO $$
DECLARE
    vrec record;
    vquery text = '';
    vselect text;
BEGIN
    FOR vrec IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE 
            table_type = 'BASE TABLE' 
            AND table_name = 'settings'
            AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')
    LOOP
        vselect = format(
            'SELECT ''%s'', prefix, id, value FROM %s.%s WHERE id = ''VERSION''',
            vrec.table_schema, quote_ident(vrec.table_schema), vrec.table_name);
        IF vquery = '' THEN vquery = vselect;
        ELSE vquery = format('%s UNION %s', vquery, vselect);
        END IF;
    END LOOP;
    EXECUTE format('COPY (%s) TO ''/tmp/out-%s'' CSV', vquery, current_database());
END $$;