Re: PostgreSQL9.1 trigger avec arguments

Bonjour,

Je bloque sur le passage d’arguments au niveau des Triggers.

J’ai plusieurs tables où je dois calculer la durée entre deux dates,
pour cela je souhaiterais créer une fonction trigger unique à toutes les
tables en passant en paramètre la date de début et la date de fin.

Ce qui est curieux c’est qu’on ne peut passer que des arguments de type
chaîne ?
J’ai donc converti les valeurs de type date en chaine de caractères
avant le passage des paramètres et fait l’opération inverse pour
calculer la durée.

Vous trouverez ci-dessous mon code et l’erreur générée.
Merci de votre aide
Martine

------------------------------------------
CREATE FUNCTION "public"."fct_calcul_duree"()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE
AS
$$
DECLARE
arg_date_debut varchar;
arg_date_fin varchar;

BEGIN
arg_date_debut := TG_ARGV[0];
arg_date_fin := TG_ARGV[1];

NEW.duree := to_date(arg_date_fin, 'YYYY-MM-DD') -
to_date(arg_date_debut, 'YYYY-MM-DD');

RETURN NEW;
END

$$
;CREATE TABLE "public"."t_projet"(
"projet_id" Serial NOT NULL,
"nom" Varchar NOT NULL,
"reference" Varchar NOT NULL,
"objectifs_scientifiques" Varchar,
"date_debut" Date NOT NULL,
"date_fin" Date,
"duree" Integer,
"observations" Varchar,
"test" Varchar,
CONSTRAINT "check_date" CHECK ((date_fin > date_debut))
)
WITH (OIDS=FALSE)

CREATE TRIGGER "trigger_calcul_duree"
BEFORE INSERT OR UPDATE OF date_debut, date_fin
ON "public"."t_projet" FOR EACH ROW
WHEN (NEW.date_fin is not null)
EXECUTE PROCEDURE
"public"."fct_calcul_duree"(to_char(NEW.date_debut,'YYYY-MM-DD'),
to_char(NEW.date_fin, 'YYYY-MM-DD'))
;

---------------------------
[WARNING ] CREATE TRIGGER "trigger_calcul_duree"
BEFORE INSERT OR UPDATE OF date_debut, date_fin
ON "public"."t_projet" FOR EACH ROW
WHEN (NEW.date_fin is not null)
EXECUTE PROCEDURE
"public"."fct_calcul_duree"(to_char(NEW.date_debut,'YYYY-MM-DD'),
to_char(NEW.date_fin, 'YYYY-MM-DD'))
ERREUR: erreur de syntaxe sur ou près de « ( »
LINE 5: ...CUTE PROCEDURE
"public"."fct_calcul_duree"(to_char(NEW.date_...
--------------------------------

Bonjour Martine,

En fait, normalement vous avez directement accès aux colonnes de la table
dans la fonction trigger via la variable automatique NEW, ce qui permet
d'éviter
de lui passer les paramètres, et de ce fait écrire directement quelque
chose du
genre :

CREATE FUNCTION "public"."fct_calcul_duree"()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE
AS
$$
BEGIN
IF NEW.date_fin IS NOT NULL THEN
NEW.duree := NEW.date_fin - NEW.date_debut;
END IF;
RETURN NEW;
END
$$

...

CREATE TRIGGER "trigger_calcul_duree"
BEFORE INSERT OR UPDATE ON "public"."t_projet"
FOR EACH ROW
EXECUTE PROCEDURE "public"."fct_calcul_duree"();

Espérant que celà puisse aider.

Cordialement.

Le 29/11/2012 17:06, Martine AGUERA a écrit :
> Bonjour,
>
> Je bloque sur le passage d’arguments au niveau des Triggers.
>
> J’ai plusieurs tables où je dois calculer la durée entre deux dates,
> pour cela je souhaiterais créer une fonction trigger unique à toutes
> les tables en passant en paramètre la date de début et la date de fin.
>
> Ce qui est curieux c’est qu’on ne peut passer que des arguments de
> type chaîne ?
> J’ai donc converti les valeurs de type date en chaine de caractères
> avant le passage des paramètres et fait l’opération inverse pour
> calculer la durée.
>
> Vous trouverez ci-dessous mon code et l’erreur générée.
> Merci de votre aide
> Martine
>
> ------------------------------------------
> CREATE FUNCTION "public"."fct_calcul_duree"()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> VOLATILE
> AS
> $$
> DECLARE
> arg_date_debut varchar;
> arg_date_fin varchar;
>
> BEGIN
> arg_date_debut := TG_ARGV[0];
> arg_date_fin := TG_ARGV[1];
>
> NEW.duree := to_date(arg_date_fin, 'YYYY-MM-DD') -
> to_date(arg_date_debut, 'YYYY-MM-DD');
>
> RETURN NEW;
> END
>
> $$
> ;CREATE TABLE "public"."t_projet"(
> "projet_id" Serial NOT NULL,
> "nom" Varchar NOT NULL,
> "reference" Varchar NOT NULL,
> "objectifs_scientifiques" Varchar,
> "date_debut" Date NOT NULL,
> "date_fin" Date,
> "duree" Integer,
> "observations" Varchar,
> "test" Varchar,
> CONSTRAINT "check_date" CHECK ((date_fin > date_debut))
> )
> WITH (OIDS=FALSE)
>
> CREATE TRIGGER "trigger_calcul_duree"
> BEFORE INSERT OR UPDATE OF date_debut, date_fin
> ON "public"."t_projet" FOR EACH ROW
> WHEN (NEW.date_fin is not null)
> EXECUTE PROCEDURE
> "public"."fct_calcul_duree"(to_char(NEW.date_debut,'YYYY-MM-DD'),
> to_char(NEW.date_fin, 'YYYY-MM-DD'))
> ;
>
> ---------------------------
> [WARNING ] CREATE TRIGGER "trigger_calcul_duree"
> BEFORE INSERT OR UPDATE OF date_debut, date_fin
> ON "public"."t_projet" FOR EACH ROW
> WHEN (NEW.date_fin is not null)
> EXECUTE PROCEDURE
> "public"."fct_calcul_duree"(to_char(NEW.date_debut,'YYYY-MM-DD'),
> to_char(NEW.date_fin, 'YYYY-MM-DD'))
> ERREUR: erreur de syntaxe sur ou près de « ( »
> LINE 5: ...CUTE PROCEDURE
> "public"."fct_calcul_duree"(to_char(NEW.date_...
> --------------------------------
>
>
>
>

--
François Figarola
-- développeur --
=================
INTERNET CONSULT
Mas Guerido
6 rue Aristide Bergès
66330 CABESTANY

Tel 04.68.66.09.29
fax 04.68.66.99.50
* francois(dot)figarola(at)i-consult(dot)fr

Bonsoir,

Martine AGUERA <martine(dot)aguera(at)laas(dot)fr> writes:
> Je bloque sur le passage d’arguments au niveau des Triggers.
>
> J’ai plusieurs tables où je dois calculer la durée entre deux dates, pour
> cela je souhaiterais créer une fonction trigger unique à toutes les tables
> en passant en paramètre la date de début et la date de fin.

Les arguments des triggers sont statiques. Ils permettent de paramétrer
une même fonction que l'on partage entre plusieurs triggers.

Pour extraire dans OLD et NEW une valeur avec un nom de champ dynamique,
fourni dans une variable de type text (TG_ARGV[0]), il est possible
d'utiliser EXECUTE et de faire du SQL dynamique. Le mieux à mon avis
reste d'utiliser l'extension hstore :

http://www.postgresql.org/docs/9.2/static/hstore.html

dim=# create extension hstore;
CREATE EXTENSION

dim=# create table foo(id serial primary key, date timestamptz default now());
CREATE TABLE

dim=# insert into foo(date) values(DEFAULT);
INSERT 0 1
dim=# insert into foo(date) values(DEFAULT);
INSERT 0 1
dim=# insert into foo(date) values(DEFAULT);
INSERT 0 1

dim=# select hstore(foo) -> 'date' from foo;
?column?
-------------------------------
2012-12-05 22:45:22.491295+01
2012-12-05 22:45:24.071532+01
2012-12-05 22:45:25.057503+01
(3 rows)

Attention cependant :

dim=# with t(x) as (select hstore(foo) -> 'date' from foo)
select x, pg_typeof(x) from t;
x | pg_typeof
-------------------------------+-----------
2012-12-05 22:45:22.491295+01 | text
2012-12-05 22:45:24.071532+01 | text
2012-12-05 22:45:25.057503+01 | text
(3 rows)

Donc dans la fonction trigger, hstore(NEW) -> TG_ARGV[0] donnera la
valeur souhaitée, mais attention, au "format" text. Il faudra donc la
convertir avant de l'utiliser pour la soustraction :

DECLARE
hstore hash := hstore(NEW);
BEGIN
NEW.duree := (hash -> TG_ARGV[1])::timestamptz
- (hash -> TG_ARGV[0])::timestamptz;
END;

Je crois que je vais transformer cela en article plus complet, merci
pour le sujet :)

Bonne continuation,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Bonjour,

Eventuellement compléter l'article par un exemple de remplacement d'un trigger équivalent à celui sous FB

Exemple sous FB:

SET TERM ^ ;

CREATE TRIGGER WS_OFFERPRODUCT_BI FOR WS_OFFERPRODUCT
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.GUID IS NULL) THEN
NEW.GUID = GEN_UUID() ;

END^

SET TERM ; ^

Impossible de trouver de la doc sur le sujet.

Merci d'avance.

Norbert Saint Georges
Six Sigma Business Intelligence Analyst

Generation ECO sprl

> -----Message d'origine-----
> De : pgsql-fr-generale-owner(at)postgresql(dot)org [mailto:pgsql-fr-generale-
> owner(at)postgresql(dot)org] De la part de Dimitri Fontaine
> Envoyé : jeudi 6 décembre 2012 00:01
> À : Martine AGUERA
> Cc : pgsql-fr-generale(at)postgresql(dot)org
> Objet : Re: [pgsql-fr-generale] PostgreSQL9.1 trigger avec arguments
>
> Bonsoir,
>
> Martine AGUERA <martine(dot)aguera(at)laas(dot)fr> writes:
> > Je bloque sur le passage d’arguments au niveau des Triggers.
> >
> > J’ai plusieurs tables où je dois calculer la durée entre deux dates,
> pour
> > cela je souhaiterais créer une fonction trigger unique à toutes les
> tables
> > en passant en paramètre la date de début et la date de fin.
>
> Les arguments des triggers sont statiques. Ils permettent de paramétrer
> une même fonction que l'on partage entre plusieurs triggers.
>
> Pour extraire dans OLD et NEW une valeur avec un nom de champ
> dynamique,
> fourni dans une variable de type text (TG_ARGV[0]), il est possible
> d'utiliser EXECUTE et de faire du SQL dynamique. Le mieux à mon avis
> reste d'utiliser l'extension hstore :
>
> http://www.postgresql.org/docs/9.2/static/hstore.html
>
> dim=# create extension hstore;
> CREATE EXTENSION
>
> dim=# create table foo(id serial primary key, date timestamptz default
> now());
> CREATE TABLE
>
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
>
> dim=# select hstore(foo) -> 'date' from foo;
> ?column?
> -------------------------------
> 2012-12-05 22:45:22.491295+01
> 2012-12-05 22:45:24.071532+01
> 2012-12-05 22:45:25.057503+01
> (3 rows)
>
> Attention cependant :
>
> dim=# with t(x) as (select hstore(foo) -> 'date' from foo)
> select x, pg_typeof(x) from t;
> x | pg_typeof
> -------------------------------+-----------
> 2012-12-05 22:45:22.491295+01 | text
> 2012-12-05 22:45:24.071532+01 | text
> 2012-12-05 22:45:25.057503+01 | text
> (3 rows)
>
> Donc dans la fonction trigger, hstore(NEW) -> TG_ARGV[0] donnera la
> valeur souhaitée, mais attention, au "format" text. Il faudra donc la
> convertir avant de l'utiliser pour la soustraction :
>
> DECLARE
> hstore hash := hstore(NEW);
> BEGIN
> NEW.duree := (hash -> TG_ARGV[1])::timestamptz
> - (hash -> TG_ARGV[0])::timestamptz;
> END;
>
> Je crois que je vais transformer cela en article plus complet, merci
> pour le sujet :)
>
> Bonne continuation,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-
> generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>

"Norbert Saint Georges" <Norbert(dot)Saint-Georges(at)generation-eco(dot)com>
writes:
> Eventuellement compléter l'article par un exemple de remplacement d'un
> trigger équivalent à celui sous FB

Il me semble voir ici (mais je ne connais pas la syntaxe FireBird, si on
parle bien de firebird) un simple trigger BEFORE INSERT. Avec PostgreSQL
il faut bien penser à ce que la fonction RETURNS TRIGGER dans sa
définition et RETURN NEW dans le code, après modification de NEW.

Quant à la génération d'UUID cela se fait avec le module associé :

http://www.postgresql.org/docs/9.2/static/uuid-ossp.html

Cependant, tout cela est complètement hors-sujet et ne sera pas dans
l'article de demain…
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

bonjour
merci pour vos réponses.
pour faire simple j'ai retenu dans un premier temps la proposition de
Francois Figarola où je teste le nom de la table qui a déclenché le
trigger et selon le cas j'utilise les noms des colonnes correspondantes.

Martine

CREATE FUNCTION "public"."fct_calcul_duree"()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE
AS
$$

BEGIN
CASE TG_TABLE_NAME
WHEN 't_projet' THEN
NEW.duree := NEW.date_fin - NEW.date_debut;
WHEN 't_processus' THEN
NEW.duree := NEW.date_cloture - NEW.date_ouverture;
END CASE;

RETURN NEW;
END

$$
;

Le 05/12/2012 22:55, Dimitri Fontaine a écrit :
> Bonsoir,
>
> Martine AGUERA<martine(dot)aguera(at)laas(dot)fr> writes:
>> Je bloque sur le passage d’arguments au niveau des Triggers.
>>
>> J’ai plusieurs tables où je dois calculer la durée entre deux dates, pour
>> cela je souhaiterais créer une fonction trigger unique à toutes les tables
>> en passant en paramètre la date de début et la date de fin.
>
> Les arguments des triggers sont statiques. Ils permettent de paramétrer
> une même fonction que l'on partage entre plusieurs triggers.
>
> Pour extraire dans OLD et NEW une valeur avec un nom de champ dynamique,
> fourni dans une variable de type text (TG_ARGV[0]), il est possible
> d'utiliser EXECUTE et de faire du SQL dynamique. Le mieux à mon avis
> reste d'utiliser l'extension hstore :
>
> http://www.postgresql.org/docs/9.2/static/hstore.html
>
> dim=# create extension hstore;
> CREATE EXTENSION
>
> dim=# create table foo(id serial primary key, date timestamptz default now());
> CREATE TABLE
>
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
> dim=# insert into foo(date) values(DEFAULT);
> INSERT 0 1
>
> dim=# select hstore(foo) -> 'date' from foo;
> ?column?
> -------------------------------
> 2012-12-05 22:45:22.491295+01
> 2012-12-05 22:45:24.071532+01
> 2012-12-05 22:45:25.057503+01
> (3 rows)
>
> Attention cependant :
>
> dim=# with t(x) as (select hstore(foo) -> 'date' from foo)
> select x, pg_typeof(x) from t;
> x | pg_typeof
> -------------------------------+-----------
> 2012-12-05 22:45:22.491295+01 | text
> 2012-12-05 22:45:24.071532+01 | text
> 2012-12-05 22:45:25.057503+01 | text
> (3 rows)
>
> Donc dans la fonction trigger, hstore(NEW) -> TG_ARGV[0] donnera la
> valeur souhaitée, mais attention, au "format" text. Il faudra donc la
> convertir avant de l'utiliser pour la soustraction :
>
> DECLARE
> hstore hash := hstore(NEW);
> BEGIN
> NEW.duree := (hash -> TG_ARGV[1])::timestamptz
> - (hash -> TG_ARGV[0])::timestamptz;
> END;
>
> Je crois que je vais transformer cela en article plus complet, merci
> pour le sujet :)
>
> Bonne continuation,

Martine AGUERA <martine(dot)aguera(at)laas(dot)fr> writes:
> pour faire simple j'ai retenu dans un premier temps la proposition de
> Francois Figarola où je teste le nom de la table qui a déclenché le trigger
> et selon le cas j'utilise les noms des colonnes correspondantes.

C'est clairement mieux.

En réalité BEFORE INSERT est trop tôt pour pouvoir exploiter le type de
données de NEW, qui n'est pas encore résolu. Cela permet de modifier le
tuple afain qu'il respecte les contraintes du type de données…

Mes essais donnent donc, logiquement :

ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function tg_duration() line 6 at assignment

La version dynamique qui fonctionne est détaillée dans l'article suivant
et je pensais pouvoir exploiter l'opérateur #= dans notre cas pratique.

http://tapoueh.org/blog/2010/11/24-dynamic-triggers-in-plpgsql.html
http://www.postgresql.org/docs/9.2/static/hstore.html

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Je crois que je vais transformer cela en article plus complet, merci
> pour le sujet :)

Et c'est enfin chose faite :

http://tapoueh.org/blog/2013/08/27-auditing-changes-with-hstore

> En réalité BEFORE INSERT est trop tôt pour pouvoir exploiter le type de
> données de NEW, qui n'est pas encore résolu. Cela permet de modifier le
> tuple afain qu'il respecte les contraintes du type de données…

Je m'étais fais avoir par une erreur pas si facile à corriger
(d'ailleurs personne ici semble n'avoir relevé) : un trigger par défaut
est FOR EACH STATEMENT, ce qui prive complètement sa procédure associée
d'accès au variables OLD et NEW, mais avec un message d'erreur étrange.

> ERROR: record "new" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.

En précisant FOR EACH ROW on s'en sort très bien. Il se trouve que j'ai
fait à nouveau l'erreur récemment et que cette fois j'ai pris le temps
de comprendre et corriger, d'où la sortie tardive d'unarticle détaillé.

En espérant que cela soit encore utile,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support