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

Please *always* mention your version of Postgres. May be important. — Erwin Brandstetter, Mar 03 '15 at 14:13

score 2 · Accepted Answer · edited May 23 '17 at 11:43

@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

I tried the above and got the following `ERROR: missing FROM-clause entry for table "ivr27258"` `LINE 2: SELECT ivr27258.tran_control_record ...` `QUERY:` `SELECT ivr27258.tran_control_record || ' ' || ch_field_name FROM ivr27258.tran_control_record WHERE ch_edit_date BETWEEN starttime AND endtime` — Deric, Mar 03 '15 at 14:02
@Deric: Sorry, the string literal wasn't properly escaped. Should be fixed now. — Erwin Brandstetter, Mar 03 '15 at 14:07
This got me 99% of the way there. The only change I had to make was altering the QUERY to `RETURN QUERY EXECUTE format( $$ SELECT %1$L || ' ' || ch_field_name FROM %1$s WHERE ch_edit_date BETWEEN '%2$s' AND '%3$s' $$, tbl_name, starttime, endtime` — Deric, Mar 03 '15 at 14:20
@Deric: Ah, right. I forgot the function parameters. Pass *values* with a `USING` clause, that's much better than concatenating them. See update above — Erwin Brandstetter, Mar 03 '15 at 14:28

score 1 · Answer 2 · edited Mar 03 '15 at 08:24

1

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;

edited Mar 03 '15 at 08:24

Erwin Brandstetter

605,456
145
1,078
1,228

answered Mar 03 '15 at 07:56

Pavel Stehule

42,331
5
91
94

PL/pgSQL Looping through multiple schema, tables and rows

2 Answers2

Much simpler yet