How to use variable as table name in plpgsql

Question

I'm new to plpgsql. I'm trying to run a simple query in plpgsql using a variable as table name in plpgsql. But the variable is being interpreted as the table name instead of the value of the variable being interpreted as variable name.

DECLARE
  v_table text;
  z_table text;
  max_id bigint;

BEGIN

FOR v_table IN
    SELECT table_name  
    FROM information_schema.tables 
    WHERE table_catalog = 'my_database' 
    AND table_schema = 'public'
    AND table_name not like 'z_%'
LOOP
    z_table := 'z_' || v_table;
    SELECT max(id) from z_table INTO max_id;
    DELETE FROM v_table where id > max_id;
END LOOP;

Some background information. For every table in my database, I have another table starting with "z_". E.g. for a table called "employee" I have identical table called "z_employee". z_employee contains the same set of data as employee. I use it to restore the employee table at the start of every test.

When I run this function I get the following error:

ERROR:  relation "z_table" does not exist
LINE 1: SELECT max(id) from z_table

My guess is that I'm not allowed to use the variable z_table in the SQL query. At least not the way I'm using it here. But I don't know how it's supposed to be done.

Arabian

Executing Dynamic Commands. – Coon

Awesome! I changed the line to EXECUTE 'SELECT max(id) from ' || z_table INTO max_id; and it worked. Too bad now I have a problem with the next line. Which was changed to EXECUTE 'DELETE FROM ' || v_table || ' WHERE id > ' || max_id;. It looks like this query chokes when there's nothing to delete. – Arabian

My English too poor to understand what you mean by 'chokes' in this context :) BTW try to use max_id as parameter like execute 'DELETE FROM ' || v_table || ' WHERE id > $1' using max_id;. – Coon

It was my mistake. I thought there was a problem when there was nothing to delete, but I realized that the problem happens when table is empty, then max_id becomes null. So I put IF max_id IS NOT NULL THEN ... to protect against this. Also thanks for the parameter suggestion. – Arabian

Please always include the function header. It's an integral part of the function. And your version of Postgres. – Marcmarcano

Answer 1

Use dynamic SQL with EXECUTE, simplify, and escape identifiers properly:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS void AS
$func$
DECLARE
   v_table text;
BEGIN
   FOR v_table IN
      SELECT table_name  
      FROM   information_schema.tables 
      WHERE  table_catalog = 'my_database' 
      AND    table_schema = 'public'
      AND    table_name NOT LIKE 'z_%'
   LOOP
      EXECUTE format('DELETE FROM %I v WHERE v.id > (SELECT max(id) FROM %I)'
                    , v_table, 'z_' || v_table);
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Table names may need to be quoted to defend against syntax errors or even SQL injection! I use the convenient format() to concatenate the DELETE statement and escape identifiers properly.

• A separate SELECT would be more expensive. You can do it all with a single DELETE statement.

Related:

• Table name as a PostgreSQL function parameter

Aside:

You might use the (slightly faster) system catalog pg_tables instead:

      SELECT tablename
      FROM   pg_catalog.pg_tables
      WHERE  schemaname = 'public'
      AND    tablename NOT LIKE 'z_%'

See:

• How to check if a table exists in a given schema

table_catalog in information_schema.tables has no equivalent here. Only tables of the current database are visible anyway. So the above predicate WHERE table_catalog = 'my_database' produces an empty result set when connected to the wrong database.

Marcmarcano