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

Hello

PostgreSQL 14 added the feature: "Allow SQL-language functions<https://www.postgresql.org/docs/14/sql-createfunction.html> and procedures<https://www.postgresql.org/docs/14/sql-createprocedure.html> 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.

Best regards
Erki Eessaar