Question 414627 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    Yes, because anonymous code blocks don’t accept parameters:

    The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

    This might work as a workaround:

    cur.execute("""
DO $$
BEGIN
  SELECT 2, {argument};
END$$;
""".format(argument=1),
)

    You can also use PERFORM instead of SELECT ... INTO if you don’t need to store the result:

    This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM.

    Login or Signup to reply.
  2. 0 
    import psycopg
from psycopg import sql

con = psycopg.connect("postgresql://postgres:[email protected]: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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search