Query tables with information_schema in pgsql 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?
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;