[SOLVED] Why are parameterized queries not possible with DO ... END?

Why are parameterized queries not possible with DO ... END?

The following works fine:

conn = psycopg.connect(self.conn.params.conn_str)
cur = conn.cursor()
cur.execute("""
    SELECT 2, %s;
    """, (1,),
)

But inside a DO:

cur.execute("""
DO $$
BEGIN
  SELECT 2, %s;
END$$;
""",  (1,),
)

it causes

psycopg.errors.UndefinedParameter: there is no parameter $1
LINE 1: SELECT 2, $1
                  ^
QUERY:  SELECT 2, $1
CONTEXT:  PL/pgSQL function inline_code_block line 3 at SQL statement

Is this expected?

Solution

import psycopg
from psycopg import sql

con = psycopg.connect("postgresql://postgres:postgres@127.0.0.1:5432/test")
cur = con.cursor()

cur.execute(sql.SQL("""
DO $$
BEGIN
  PERFORM 2, {};
END$$;
""").format(sql.Literal(1))
)

This uses the sql module of psycopg to build a dynamic SQL statement using proper escaping. DO can't return anything so you will not get any result from the function.