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?

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

4

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Jun 25 '15 at 05:14

Erwin Brandstetter

605,456
145
1,078
1,228

It works! Thanks for such as great explanation. So, the key is using `_tbl text := _t::text;` to persist the name? Great example! – noober Jun 25 '15 at 05:41
@noober That's not sufficient, because `regclass` to name resolution will schema-qualify and identifier-quote where necessary. Observe: `CREATE SCHEMA someschema; CREATE TABLE someschema."MyTable"(); SELECT 'someschema."MyTable"'::regclass::text;`. If you simply append to the identifier you'll get bogus results. You really have to extract the schema- and name-parts, modify each individually, and identifier-quote them (or use `format` with `%I`). – Craig Ringer Jun 25 '15 at 08:20
@CraigRinger: Well, theoretically. But as long as he just passes a table name with no schema qualification to the function, it would raise an exception if that table was not found in the `search_path`. Consequently, `_t::text` is returning the same table name without schema-qualification, so the problems with added schema-name cannot occur. – Erwin Brandstetter Jun 25 '15 at 08:46
@ErwinBrandstetter Yep, and the same is true for `"Quoted Identifiers"`. So it might not matter but is IMO worth being aware of if using the regclass pseudo-type. – Craig Ringer Jun 25 '15 at 08:53
@CraigRinger: It certainly is. I might just use a `text` parameter instead as suggested. But there may the the requirement to check if the argument is a valid table name before doing anything else. – Erwin Brandstetter Jun 25 '15 at 08:59
@ErwinBrandstetter - is it possible when calling this to not enclose the tablename with a single quote? i.e. `select foo(mytable);` instead of `select foo('mytable')` – noober Jun 25 '15 at 17:56
@noober: Not like this. But there are related techniques with a polymorphic type, where you pass `NULL::mytable` and extract the type name (among other things) in the function body. Details: http://stackoverflow.com/a/11751557/939860 (last chapter). If that's still unclear, start a new question with all the necessary details. You can always reference this one for context. – Erwin Brandstetter Jun 25 '15 at 18:05

Unexpected behavior of dynamic SQL using regclass

1 Answers1