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?
\gexec
in psqlIf 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:
TABLE my_view
is short syntax for SELECT * FROM my_view
. See:
Related:
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.