Preparing CREATE statement

Question

I try to have a view created on the fly.
In my case it's a pivot view on a EAV-style table.

Assuming a table which contains two attributes I use a statement like this, executed inside of a trigger function (written in PL/Python):

CREATE OR REPLACE VIEW pivot_eav AS 
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''attr1''), (''attr2'')
    '::text
    )
  AS ct(id    text,
        attr1 text,
        attr2 text
    );

But now given I don't know the names of the two attributes at creation time. I will have to insert them at runtime. I can build the statement on the fly and execute it. Actually that's what I do at the moment:

"""
CREATE OR REPLACE VIEW pivot_eav AS 
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''{0}''), (''{1}'')
    '::text
    )
  AS ct(id text,
        {0} text,
        {1} text
    );
""".format("attr1", "attr2")

But I'm aware that this is highly insecure, because input is executed.

I tried to use a prepared statement like this:

CREATE OR REPLACE VIEW pivot_eav AS 
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''$1''), (''$2'')
    '::text
    )
  AS ct(id text,
        $1 text,
        $2 text
    );

which doesn't seem to be legal.

I was able to circumvent the first to placeholder by using dollar sign escaping, but still the second occurrences seem not to e legal in those positions!?

I also wasn't able to use dollar escaping in that place, because the names are use as literal there.

My question:
Is it correct, that placeholder in prepared statements only have special uses? (And can be used in arbitrary positions like string interpolation in programming languages.)
If so, is there another, secure, way to put that statement together?

Robin

Answer 1

DDL queries, including CREATE statements, cannot be prepared.

Moreover, even in DML queries that can be prepared, parameters cannot be used for identifiers. They're allowed at places in the query where a literal would be allowed.

As an alternative, you can safely inject column names into a query with the dedicated function that PL/Python provides for dynamic SQL: plpy.quote_ident(string):

https://www.postgresql.org/docs/current/static/plpython-util.html