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