Executing a query created by another query

Question

I have several views on the same table containing overlapping data. By dynamically retrieving the views from the information_schema and unioning them I can create a text query result. However I still need to manually run that query to get the desired result.

This is the query I use to build the union query:

SELECT string_agg('SELECT * FROM '||TABLE_NAME, ' UNION ')::TEXT 
FROM INFORMATION_SCHEMA.VIEWS 
where TABLE_NAME like '%value%'

I did an initial attempt with executing the query and storing it in a temporary table, however no success yet:

do $$
declare
    q text;
begin
    q := format('SELECT string_agg(''SELECT * FROM ''||TABLE_NAME, '' UNION '')::TEXT FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME like ''%value%''');
    execute format('create or replace temp view t ', q);
end $$;
select * from t;

How can I execute the created query and get the actual result?

Answer 1

\gexec in psql

If you operate from the default command line terminal psql (or call that from the shell), use the \gexec meta-command to execute dynamic SQL directly:

SELECT string_agg('TABLE ' || quote_ident(table_name), ' UNION ')
FROM   information_schema.views
WHERE  table_name LIKE '%value%'
AND    table_schema = 'public'  -- your schema here!
\gexec

No need for a temporary table in this case.
Be explicit about the schema, or you might include other objects by accident.
Identifiers listed in catalog tables and the information schema views may require double-quoting. quote_ident() or format() with the specifier %I take care of it. Else you are open to SQL injection! See:

• SQL injection in Postgres functions vs prepared queries

TABLE my_view is short syntax for SELECT * FROM my_view. See:

• Is there a shortcut for SELECT * FROM?

Related:

• Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
• Generate matrix of access in source-target table

Pure SQL

Else we are back to your pure SQL approach. Fixed & improved:

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          'DROP VIEW IF EXISTS pg_temp.t; CREATE TEMP VIEW t AS '
       || string_agg('TABLE ' || format('%I.%I', table_schema, table_name), ' UNION ')
   FROM   information_schema.views
   WHERE  table_name ~ 'value'
   AND    table_schema = 'public';

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   ELSE
      RAISE WARNING 'No views found!';  -- or whatever you need to do here
   END IF;
END
$do$;

TABLE pg_temp.t;  -- table-qualify to make sure

It's good form to table-qualify temporary objects to be sure you don't target a persisted object of the same name by accident.

Answer 2

Here's an example (you can build up q as you need):

do $$

declare
    q text;
begin
    SELECT 'DROP VIEW IF EXISTS t; CREATE VIEW t AS ' ||  STRING_AGG('SELECT '''||table_name||''' AS col1 ', 'union ') AS dyn_sql
    INTO q
    FROM INFORMATION_SCHEMA.TABLES;
    EXECUTE q;
end $$;
select * from t;