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.
Best regards
Erki Eessaar
