Dynamic queries with format() using the same argument many times

Question

I have a procedure which takes as input a suffix of a table's name. Then, using execute format(), I pass this parameter to execute the dynamic query. The problem is that this parameter is the same throughout - I do not want to pass it x times as such:

execute format('SELECT table_%s.field1, table_%s.field2,table_%s.field3
FROM table_%s', inTableSuffix, inTableSuffix, inTableSuffix, inTableSuffix, ...)

I would like a format similar to the following:

execute format('SELECT table_%s.field1, table_%s.field2,table_%s.field3
FROM table_%s', inTableSuffix)

I understand that I can solve this problem using an alias to the table name but is there another alternative?

So do you have your answer? – Erwin Brandstetter Jan 18 '17 at 20:22 — Erwin Brandstetter, Jan 18 '17 at 20:22

wildplasser · Answer 1 · 2017-01-14T15:55:36.283

6

You can repeatedly address the positional arguments like this:

execute format('SELECT table_%1$s.field1
   , table_%1$s.field2,table_%1$s.field3
FROM table_%1$s;', inTableSuffix);

Note: in this particular case, you can avoid repeating yourself by using an alias:

execute format('SELECT tx.field1
   , tx.field2, tx.field3
FROM table_%s tx;', inTableSuffix);

edited Jan 14 '17 at 15:55

answered Jan 14 '17 at 15:26

wildplasser

43,142
8
66
109

Also, there is no need to use the table name for each column if there is not a join, but even if there a join you can use alias to the table, so there not need to concat multiple times the full table name. – llouk Jan 14 '17 at 15:54

score 1 · Answer 2 · edited May 23 '17 at 11:46

@wildplasser already provided the syntax how to reference the same parameter repeatedly in format(). The code is still dangerous, though. You need to escape identifiers built from user input:

EXECUTE format('SELECT field1, field2, field3 FROM %I', 'table_' || inTableSuffix);

For the given example, you only need the parameter once anyway. The scope of dynamic queries executed with EXECUTE in plpgsql is limited to the query itself. Other variables or parameters of the function are not visible inside EXECUTE. Hence, there is not need to table-qualify columns in a dynamic query with a single table in the FROM clause.

But more importantly, %I is used in format() for identifiers to avoid syntax errors, misguided lower-casing or, worse, SQL injection! It double-quotes (only!) otherwise illegal identifiers, just like quote_ident() would.

Details:

@wildplasser: You seem to be missing that I do that already. No need for a (more expensive) variable, you can pass the concatenated expression as parameter. Aside: it's `quote_ident()`, not `quote_identifier()`. — Erwin Brandstetter, Jan 14 '17 at 16:37

Dynamic queries with format() using the same argument many times

2 Answers2