Re: References to parameters by name are lost in INSERT INTO ... SELECT <parameter value> .... statements in case of routines with the SQL-standard function body

On Fri, Nov 12, 2021 at 4:46 AM Erki Eessaar <erki(dot)eessaar(at)taltech(dot)ee> wrote:
>
> Hello
>
> PostgreSQL 14 added the feature: "Allow SQL-language functions and procedures to use SQL-standard function bodies."
>
> If the routine contains INSERT INTO ... SELECT <parameter value> ... statement, then \sf command in psql and pg_get_functiondef function return a CREATE statement where in the SELECT statement the references to the parameters by name have been replaced with positional references.
>
> An example.
>
> CREATE TABLE Person (person_id INTEGER,
> e_mail VARCHAR(254) NOT NULL,
> last_action TIMESTAMP,
> CONSTRAINT pk_person PRIMARY KEY (person_id),
> CONSTRAINT ak_person UNIQUE (e_mail));
>
> CREATE TABLE Product (product_code INTEGER,
> registrator_id INTEGER NOT NULL,
> price NUMERIC(19,4) NOT NULL,
> CONSTRAINT pk_product PRIMARY KEY (product_code),
> CONSTRAINT fk_product_person FOREIGN KEY (registrator_id) REFERENCES Person(person_id));
>
> CREATE OR REPLACE FUNCTION f_reg_product (p_product_code Product.product_code%TYPE, p_price Product.price%TYPE, p_e_mail Person.e_mail%TYPE)
> RETURNS VOID
> LANGUAGE SQL SECURITY DEFINER
> SET search_path=public, pg_temp
> BEGIN ATOMIC
> INSERT INTO Product (product_code, price, registrator_id)
> SELECT p_product_code, p_price, person_id
> FROM Person
> WHERE e_mail=p_e_mail;
> UPDATE Person SET last_action=LOCALTIMESTAMP(0) WHERE e_mail=p_e_mail;
> END;
>
> SELECT pg_get_functiondef(oid) AS func_def
> FROM pg_proc
> WHERE proname='f_reg_product';
>
> The result.
>
> CREATE OR REPLACE FUNCTION public.f_reg_product(p_product_code integer, p_price numeric, p_e_mail character varying)
> RETURNS void
> LANGUAGE sql
> SECURITY DEFINER
> SET search_path TO 'public', 'pg_temp'
> BEGIN ATOMIC
> INSERT INTO product (product_code, price, registrator_id) SELECT $1 AS p_product_code,
> $2 AS p_price,
> person_id
> FROM person
> WHERE ((e_mail)::text = ($3)::text);
> UPDATE person SET last_action = LOCALTIMESTAMP(0)
> WHERE ((person.e_mail)::text = (f_reg_product.p_e_mail)::text);
> END
>
> As you can see, the issue does not affect the UPDATE statement.

As you mentioned, p_e_mail in the UPDATE statement is not replaced
with a positional reference. But 'f_reg_product.p_e_mail' in the
UPDATE statement seems to correctly refer to the function argument
'p_e_mail'. Does the execution of the function produced by
pg_get_functiondef() produce a different result from the original's
one?

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/