Question 136919 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Executing a query created by another query

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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:

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

    Related:

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search