Unexpected behavior of dynamic SQL using regclass

Question

I have some weird behavior using EXECUTE with regclass and I'm trying to debug this and need some help.

Basically, I'm trying to run these SQL statements within a function:

ALTER TABLE mytable_bak RENAME TO mytable_old;
TRUNCATE TABLE mytable_old;
ALTER TABLE mytable RENAME TO mytable_bak;
ALTER TABLE mytable_old RENAME TO mytable;

Here's my function (not working as expected):

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||'_bak RENAME TO '|| _t || '_old';
   EXECUTE 'TRUNCATE TABLE '|| _t || '_old';
   EXECUTE 'ALTER TABLE '|| _t ||' RENAME TO '|| _t || '_bak';
   EXECUTE 'ALTER TABLE '|| _t ||'_old RENAME TO '|| _t;
END
$func$ LANGUAGE plpgsql;

It doesn't like this last line when I execute:

EXECUTE 'ALTER TABLE '|| _t ||'_old RENAME TO '|| _t;

For example:

foo_bar_12345=> select foo('mytable');
ERROR:  relation "mytable_bak_old" does not exist
CONTEXT:  SQL statement "ALTER TABLE mytable_bak_old RENAME TO mytable_bak"
PL/pgSQL function foo(regclass) line 6 at EXECUTE statement

It's as if the 3rd execute is cached, holding the table name.

What's interesting: if I remove the last line and execute it, it works as expected to that point, but I still need this last line (code above) to execute:

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||'_bak RENAME TO '|| _t || '_old';
   EXECUTE 'TRUNCATE TABLE '|| _t || '_old';
   EXECUTE 'ALTER TABLE '|| _t ||' RENAME TO '|| _t || '_bak';
END
$func$ LANGUAGE plpgsql;

What am I missing here? Especially with this last statement?

Answer 1

The object identifier data type regclass internally is the oid of the system catalog table pg_class. The string 'mytable' you are passing as argument is resolved to the object identifier immediately in the "convenience cast" to regclass. If you later rename the table, _t resolves to the new name in the next call.

• _t is renamed to mytable_bak in the 3rd EXECUTE.
• The error happens in your 4th EXECUTEwhere _t is resolved to mytable_bak (correctly!) and you end up trying to rename a table mytable_bak_old - as you can see in the error message.

Extract the table name once before you start your naming charade:

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
DECLARE
  _tbl text := _t::text;  -- "early binding"
BEGIN
   EXECUTE format('ALTER TABLE %I_bak RENAME TO %1$s_old', _tbl);
   EXECUTE 'TRUNCATE TABLE ' || _tbl || '_old';
   EXECUTE format('ALTER TABLE %1$s RENAME TO %1$s_bak', _tbl);
   EXECUTE format('ALTER TABLE %1$s_old RENAME TO %1$s', _tbl);
END
$func$ LANGUAGE plpgsql;

Tested and works for me in Postgres 9.4.

Note that this only works for legal, lower-case table names that don't need double-quoting and are visible in the search_path. Else you'll get an error message - you'd need to do more to concatenate names properly. SQL-injection is not possible, though.

Or just pass a text string and escape it with quote_ident() inside:

• SQL injection in Postgres functions vs prepared queries