How to compose a psycopg2 dynamic SQL statement that includes functions on column names? e.g.: json_col::text

Question

I need to compose a dynamic SQL statement, which includes certain functions on column names. E.g.: SELECT json_col::text, SUBSTRING ( string_col ,1 , 2 ) FROM TABLE

Since the column names are enclosed in "", I keep getting undefined column error. What is the best way to compose a dynamic SQL with functions on column names?

Answer 1

You could add the the SQL functions to your SQL statement:

cols = ["column1", "column2", "column3"]
table = "myTable"
query = sql.SQL(
    "select {0}::text, upper({1}), lower({2}), {3}::jsonb from {4} where id = %s").format(
        sql.Identifier("id"),
        sql.Identifier(cols[0]),
        sql.Identifier(cols[1]),
        sql.Identifier(cols[2]),
        sql.Identifier(table)
)
print(cur.mogrify(query, (240, )).decode('utf-8'))

Out:

select "id"::text, upper("column1"), lower("column2"), "column3"::jsonb from "myTable" where id = 240