How to run the prepared statement with arguments dynamically in a function?

Question

I created person table, then inserted 2 rows into it as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

Then, I created the prepared statement my_pre with a PREPARE statement which can update age with id in person table as shown below:

PREPARE my_pre(INT, INT) AS
  UPDATE person SET age = $1 WHERE id = $2;

Then, I created my_func() with the EXECUTE statement which has the EXECUTE statement to run my_pre($1, $2) dynamically as shown below:

CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre($1, $2)' USING age, id;
END;
$$ LANGUAGE plpgsql;

But, calling my_func() got the error as shown below:

postgres=# SELECT my_func(45, 2);
ERROR:  there is no parameter $1
LINE 1: EXECUTE my_pre($1, $2)
                       ^
QUERY:  EXECUTE my_pre($1, $2)
CONTEXT:  PL/pgSQL function my_func(integer,integer) line 3 at EXECUTE

Actually, when I directly set 45 and 2 to my_pre in my_func() as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre(45, 2)';
END;                   -- ↑↑  ↑
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error, then age of David was updated to 45 as shown below:

postgres=# SELECT my_func();
 my_func
---------

(1 row)

postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  45
(2 rows)

Or, I created my_func1() which can update age with id in person table as shown below:

CREATE FUNCTION my_func1(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING age, id;
END;
$$ LANGUAGE plpgsql;

Then, I created my_func2() with the EXECUTE statement which has the SELECT statement to call my_func1($1, $2) dynamically as shown below:

CREATE FUNCTION my_func2(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'SELECT my_func1($1, $2)' USING age, id;
END;
$$ LANGUAGE plpgsql;

Then, I could call my_func2() without error, then age of David was updated to 45 as shown below:

postgres=# SELECT my_func2(45, 2);
 my_func2
----------

(1 row)

postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  45
(2 rows)

So, how can I run the prepared statement my_pre($1, $2) dynamically in my_func()?

1) Why are you using EXECUTE my_pre() in a function anyway? 2) Especially as per plpgsql EXECUTE: The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server's EXECUTE statement cannot be used directly within PL/pgSQL functions (and is not needed).. — Adrian Klaver
– Adrian Klaver, Commented Feb 17, 2024 at 17:11

Pavel Stehule · Accepted Answer · 2024-02-18 05:43:21Z

The SQL prepared statements should not be executed from PL/pgSQL. Every embedded SQL is prepared by default. The EXECUTE command from PL/pgSQL is different command than SQL EXECUTE command (some other databases uses different access, but Postgres uses API similar to Oracle).

Unfortunately, SQL statement EXECUTE doesn't allow to use parameters (only SELECT and CALL statements allows parameters):

(2024-02-18 06:27:01) postgres=# execute x($1,$2) \bind 10 20 \g
ERROR:  08P01: bind message supplies 2 parameters, but prepared statement "" requires 0
LOCATION:  exec_bind_message, postgres.c:1716

So you cannot to pass parameters from PL/pgSQL EXECUTE. You can do it on query level by using format function:

EXECUTE format('execute x(%L, %L)', age, id);

This will be safe too.

The model for dynamic SQL is different than model used by some other databases. The reason is simple. PL/pgSQL statement does preparing and execution together, and then doesn't except so it will be call already prepared statement. Maybe the Oracle design is less messy, because they use syntax EXECUTE IMMEDIATELY, but it is too late to change it now. Today is possible to enhance SQL EXECUTE to support parametrization (the SQL statement CALL do this), but nobody wrote it (probably there is not too strong, too interesting use case).

Collectives™ on Stack Overflow

How to run the prepared statement with arguments dynamically in a function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related