Query tables with information_schema in pgsql

Question

I am trying to query the tables contained in a schema from an insert trigger on another table.

My process is :

Manually create the schema with DB Manager inside QGIS.
Load tables foo, bar, baz.
Insert a reference to that schema inside a table which trigger a function to perform queries on these tables.

I am fine to create a trigger/trigger function, but unable to create the query to grab the information inside the tables

EDIT :

WITH info_schema_subset_table as (SELECT table_schema,table_name, 
array_to_string((regexp_split_to_array(table_name,'_'))[4:array_length(regexp_split_to_array(table_name,'_'),1)-1],'_')) END as new_table
FROM information_schema.tables
where table_schema = NEW,schema_name
ORDER BY new_table ASC)
EXECUTE 'CREATE TABLE $2 as (SELECT * FROM $1);'
USING info_schema_subset_table.table_schema || '.' ||info_schema_subset_table.table_name,info_schema_subset_table.new_table;

I am unable to run a simple execute. What am I missing?

sticky bit · Answer

Sounds like you need dynamic SQL for this. First build your query using string concatenation with the desired values (e.g. schema name, table name), then execute it with EXECUTE.

Example:

-- create example objects
    CREATE SCHEMA example_schema;
    CREATE TABLE example_schema.example_table1 (example_column integer);
    CREATE TABLE example_schema.example_table2 (example_column integer);
    CREATE TABLE example_schema.example_table3 (example_column integer);

-- example anonymous block using dynamic queries
    DO
    $$
    DECLARE
      dynamic_query text;
      catalog_row record;
      counter integer = 0;
    BEGIN
      -- insert an integer, that's incremented for every iteration into the example tables
      FOR catalog_row IN SELECT table_schema || '.' || table_name qualified_table_name
                                FROM information_schema.tables
                                WHERE table_schema = 'example_schema'
                                      AND table_name LIKE 'example_table%' LOOP
        counter := counter + 1;
        -- building the query;
        dynamic_query := 'INSERT INTO ' || catalog_row.qualified_table_name || ' VALUES (' || counter || ')';
        -- executing the query;
        EXECUTE dynamic_query;
      END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

-- check if it has worked
    SELECT * FROM example_schema.example_table1
    UNION ALL
    SELECT * FROM example_schema.example_table2
    UNION ALL
    SELECT * FROM example_schema.example_table3;

Query tables with information_schema in pgsql

One Answer

Add your own answers!

Ask a Question