How to quote qualified table name with format() function?

Question

I have some problems in properly quoting a table name using the format function in the example below.

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
 RETURNS bool AS $func$
DECLARE query_str text; 
BEGIN
  query_str = format($fmt$ DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS (TABLE %s); $fmt$, _target_tbl, _source_tbl);
  EXECUTE query_str;
  RAISE NOTICE '%', query_str;
 RETURN True;
END $func$ LANGUAGE plpgsql;

My dilemma is that I want to quote the input table name _target_tbl as an identifier (to avoid SQL injection). However, given a full table name ex.test1, this causes the schema part ex. to be treated as part of the table name and the table public."ex.test1" created in the default public. schema as shown below.

How should I quote/format the identifier properly here?

=> SELECT copy_table('ex.test', 'ex.test1');
NOTICE:  table "ex.test1" does not exist, skipping
NOTICE:   DROP TABLE IF EXISTS "ex.test1"; CREATE TABLE "ex.test1" AS (TABLE ex.test);

=> \dt ex.test1
Did not find any relation named "ex.test1".
=> \dt "ex.test1"
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 public | ex.test1 | table | 
(1 row)

This is with PostgreSQL 10.3.

Erwin Brandstetter · Answer 1 · 2018-03-16T02:42:46.130

To avoid ambiguity in the target table name, provide names of schema and table separately. With existing tables Postgres can use the current search_path to default to the first schema with an object of that name for unqualified table names. Since that's obviously not possible for (yet) non-existing tables, we have to be more explicit there.

If you typically want to place the target table in the same schema as the source, you can still omit the schema name and make the function default to the schema of the (existing!) source table for convenience. Like:

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass
                                    , _target_tbl text
                                    , _target_schema text = NULL)
  RETURNS bool AS
$func$
DECLARE
   query_str text; 
BEGIN
   IF _target_schema IS NULL THEN               -- if no target schema provided ...
      SELECT c.relnamespace::regnamespace::text -- ... default to schema of input table
      FROM   pg_class c
      WHERE  c.oid = _source_tbl
      INTO  _target_schema;
   END IF;

   query_str = format('DROP TABLE IF EXISTS %1$I.%2$I;
                       CREATE TABLE %1$I.%2$I AS (TABLE %3$s);'
    , _target_schema
    , _target_tbl
    , _source_tbl);

   EXECUTE query_str;
   RAISE NOTICE '%', query_str;
   RETURN true;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT copy_table('ex.test', 'text1', 'ex');

Since the 3rd (last) parameter _target_schema has a default value, we can omit it, in which case the function defaults to the schema of the source table:

SELECT copy_table('ex.test', 'test1');

Even if we don't provide the source schema explicitly, relying on the current search_path:

SELECT copy_table('test', 'test1');

Table name as a PostgreSQL function parameter

score 0 · Answer 2 · answered Mar 16 '18 at 03:26

You've got an issue here where you're recreating a table LIKE another table. I can't see why that would ever be a good idea. Normally when I see this kind of stuff, it's an attempt at creating a multi-tenant solution. I think you're going about the wrong way. As a totally different suggestion, check out Row-Level Security. If you need to ensure users have a copy of a master-table, it would seem preferable to simply use the master table.

I also can't see how it's a sane idea to just DROP IF EXISTS another table. If that table is a transient temporary table, you should look into CREATE TEMPORARY TABLE -- then all you have to do is disconnect, and recreate it if needed (CREATE TEMPORARY TABLE IF NOT EXISTS).

By any means, I'm just speculating. More details could be provided in another question to get a more appropriate answer.

How to quote qualified table name with format() function?

2 Answers2

Linked