skip to Main Content

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?

2

Answers


  1. 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. 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