PL/pgSQL Looping through multiple schema, tables and rows

Question

I have a database with multiple identical schemas. There is a number of tables all named 'tran_...' in each schema. I want to loop through all 'tran_' tables in all schemas and pull out records that fall within a specific date range. This is the code I have so far:

CREATE OR REPLACE FUNCTION public."configChanges"(starttime timestamp, endtime timestamp)
  RETURNS SETOF character varying AS
$BODY$DECLARE
    tbl_row RECORD;
    tbl_name VARCHAR(50);
    tran_row RECORD;
    out_record VARCHAR(200);
BEGIN
    FOR tbl_row IN
       SELECT * FROM pg_tables WHERE schemaname LIKE 'ivr%' AND tablename LIKE 'tran_%'
    LOOP
        tbl_name := tbl_row.schemaname || '.' || tbl_row.tablename;
         FOR tran_row IN
            SELECT * FROM tbl_name
            WHERE ch_edit_date >= starttime AND ch_edit_date <= endtime
         LOOP
            out_record := tbl_name || '    ' || tran_row.ch_field_name;
                        RETURN NEXT out_record;
         END LOOP;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

When I attempt to run this, I get:

ERROR:  relation "tbl_name" does not exist
LINE 1: SELECT * FROM tbl_name WHERE ch_edit_date >= starttime AND c...

Answer 1

@Pavel already provided a fix for your basic error.

However, since your tbl_name is actually schema-qualified (two separate identifiers in : schema.table), it cannot be escaped as a whole with %I in format(). You have to escape each identifier individually.

Aside from that, I suggest a different approach. The outer loop is necessary, but the inner loop can be replaced with a simpler and more efficient set-based approach:

CREATE OR REPLACE FUNCTION public.config_changes(_start timestamp, _end timestamp)
  RETURNS SETOF text AS
$func$
DECLARE
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT quote_ident(schemaname) || '.' || quote_ident(tablename)
      FROM   pg_tables
      WHERE  schemaname LIKE 'ivr%'
      AND    tablename LIKE 'tran_%'
   LOOP
      RETURN QUERY EXECUTE format (
         $$
         SELECT %1$L || '    ' || ch_field_name
         FROM   %1$s
         WHERE  ch_edit_date BETWEEN $1 AND $2
         $$, _tbl
         )
      USING _start, _end;
   END LOOP;
   RETURN;
END
$func$  LANGUAGE plpgsql;

• You have to use dynamic SQL to parametrize identifiers (or code), like @Pavel already told you. With RETURN QUERY EXECUTE you can return the result of a dynamic query directly. Examples:

  • Return SETOF rows from PostgreSQL function
  • Refactor a PL/pgSQL function to return the output of various SELECT queries

• Remember that identifiers have to be treated as unsafe user input in dynamic SQL and must always be sanitized to avoid syntax errors and SQL injection:

  • Table name as a PostgreSQL function parameter

• Note how I escape table and schema separately:

   quote_ident(schemaname) || '.' || quote_ident(tablename)
  

• Consequently I just use %s to insert the already escaped table name in the later query. And %L to escape it a string literal for output.

• I like to prepend parameter and variable names with _ to avoid naming conflicts with column names. No other special meaning.

There is a slight difference compared to your original function. This one returns an escaped identifier (double-quoted only where necessary) as table name, e.g.:

"WeIRD name"

instead of

WeIRD name

Much simpler yet

If possible, use inheritance to obviate the need for above function altogether. Complete example:

• Select (retrieve) all records from multiple schemas using Postgres

Answer 2

You cannot use a plpgsql variable as SQL table name or SQL column name. In this case you have to use dynamic SQL:

FOR tran_row IN
   EXECUTE format('SELECT * FROM %I
                   WHERE ch_edit_date >= starttime AND ch_edit_date <= endtime', tbl_name)
LOOP
   out_record := tbl_name || '    ' || tran_row.ch_field_name;
   RETURN NEXT out_record;
END LOOP;