Function trigger with count in postgresql-CodePudding

I have created some schemas (dsfv, dsfn, etc.) and insert some tables inside each schema, including the following tables :

poste_hta_bt (parent table with attribute "code_pt" as unique key);
transfo_hta_bt (having also "code_pt" attribute as foreign key that referenced poste_hta_bt).

I have also created a function trigger that count the total number of entities from "transfo_hta_bt" and report it in "nb_transf" attribute of "poste_hta_bt".

My code is as follows :

SET SESSION AUTHORIZATION dsfv;
SET search_path TO dsfv, public;

CREATE TABLE IF NOT EXISTS poste_hta_bt
(
    id_pt serial NOT NULL,
    code_pt varchar(30) NULL UNIQUE,
    etc.
);
CREATE TABLE IF NOT EXISTS transfo_hta_bt
(
    id_tra serial NOT NULL,
    code_tra varchar(30) NULL, 
    code_pt varchar(30) NULL,
    etc.
);

ALTER TABLE transfo_hta_bt ADD CONSTRAINT "FK_transfo_hta_bt_poste_hta_bt"
FOREIGN KEY (code_pt) REFERENCES poste_hta_bt (code_pt) ON DELETE No Action ON UPDATE No Action;

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    som_transf smallint;
    som_transf1 smallint;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = NEW.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = NEW.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'DELETE') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = OLD.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = OLD.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'UPDATE') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = NEW.code_pt;
        SELECT COUNT(*) INTO som_transf1 FROM dsfv.transfo_hta_bt WHERE code_pt = OLD.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = NEW.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf1 WHERE dsfv.poste_hta_bt.code_pt = OLD.code_pt;
        RETURN NULL;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;

DROP TRIGGER IF EXISTS recap_tr ON dsfv.transfo_hta_bt;
CREATE TRIGGER recap_tr AFTER INSERT OR UPDATE OR DELETE ON dsfv.transfo_hta_bt FOR EACH ROW EXECUTE PROCEDURE recap_transf();

This code runs correctly but I didn't understand why the following: In the function trigger, I noticed that I have to specify the schema of each table, despite that I adjusted search_path to dsfv from the beginning. Also when I replace dsfv.transfo_hta_bt with TG_TABLE_NAME in the function trigger, the lattest variable is unrecognized. Thank you in advance for your help.

CodePudding user response：

PostgreSQL stores a function as string, which is interpreted when the function is executed. The search_path that applies is the one in effect when the function is called, not the one in effect when it is created. (SQL functions created with the new syntax from v14 are safe from that, since they are parsed when the function is created.)

To avoid these problems, you should fix the search_path for all functions:

ALTER FUNCTION recap_transf() SET search_path = dsfv;

Note that it is not safe to add a schema where untrusted users can create objects, so only add public if you revoked the CREATE privilege for PUBLIC that it has in versions before v15.

CodePudding user response：

You need to repeat SET search_path TO dsfv, public; at the beginning of the function body for it to apply to its inner context.

TG_TABLE_NAME is a text variable not unlike TG_OP that you are already using, so you can't directly plug it in a query as a table name. You'd have to construct your queries as text and use dynamic SQL EXECUTE to run them.

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    som_transf smallint;
    som_transf1 smallint;
BEGIN
--search path update is rendered somewhat useless by dynamic SQL used later
    execute 'SET search_path TO '||TG_TABLE_SCHEMA||', public;';
    IF (TG_OP = 'INSERT') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using NEW.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      NEW.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'DELETE') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using OLD.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      OLD.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'UPDATE') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using NEW.code_pt;
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf1 
                using OLD.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      NEW.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf1,
                      OLD.code_pt;
        RETURN NULL;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;