Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Triggers

SQL Triggers

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
Is this an error on my part or is there a bug in the CREATE TRIGGER
code?

darcy=> CREATE FUNCTION function_dead(int)
darcy->     RETURNS int
darcy->     AS 'UPDATE bid SET live = \'f\' WHERE item_id = $1;
darcy'>         SELECT 1 AS ignore_this'
darcy->     LANGUAGE 'sql';
CREATE
darcy=> CREATE TRIGGER trigger_dead BEFORE INSERT OR UPDATE
darcy->     ON bid
darcy->     FOR EACH ROW
darcy->     EXECUTE PROCEDURE function_dead (item_id);
ERROR:  CreateTrigger: function function_dead () does not exist
darcy=> SELECT function_dead(1);
function_dead
-------------
            1
(1 row)

darcy=> DROP FUNCTION function_dead(int);
DROP
darcy=>

Note that the CREATE FUNCTION works as it can be used as in the SELECT
and it can be dropped proving (?) that it existed.  I think I have
all the argument types right.  I tried it with int4 as well.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Re: [HACKERS] SQL Triggers

From
jwieck@debis.com (Jan Wieck)
Date:
>
> Is this an error on my part or is there a bug in the CREATE TRIGGER
> code?
>
> darcy=> CREATE FUNCTION function_dead(int)
> darcy->     RETURNS int
> darcy->     AS 'UPDATE bid SET live = \'f\' WHERE item_id = $1;
> darcy'>         SELECT 1 AS ignore_this'
> darcy->     LANGUAGE 'sql';
> CREATE
> darcy=> CREATE TRIGGER trigger_dead BEFORE INSERT OR UPDATE
> darcy->     ON bid
> darcy->     FOR EACH ROW
> darcy->     EXECUTE PROCEDURE function_dead (item_id);
> ERROR:  CreateTrigger: function function_dead () does not exist
> darcy=> SELECT function_dead(1);
> function_dead
> -------------
>             1
> (1 row)
>
> darcy=> DROP FUNCTION function_dead(int);
> DROP
> darcy=>
>
> Note that the CREATE FUNCTION works as it can be used as in the SELECT
> and it can be dropped proving (?) that it existed.  I think I have
> all the argument types right.  I tried it with int4 as well.
>
> --
> D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
> http://www.druid.net/darcy/                |  and a sheep voting on
> +1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.
>
>

    The  arguments  aren't right and the return type too. Trigger
    procedures take no arguments in their definition  and  return
    OPAQUE type. They really return a tuple of the table they are
    actually fired for.

    Take a look at PL/pgSQL and it's docs which is already in the
    CVS and will be shipped with 6.4.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Re: [HACKERS] SQL Triggers

From
David Hartwig
Date:
Jan,

I am dying to take our (your) new procedural language for a spin.  Just one
problem.   I don't see a Makefile in the pl/plpgsql/src directory.   Am I
missing something in my configure?

Jan Wieck wrote:

>     Take a look at PL/pgSQL and it's docs which is already in the
>     CVS and will be shipped with 6.4.
>

PL/pgSQL Makefile (was: Re: [HACKERS] SQL Triggers)

From
jwieck@debis.com (Jan Wieck)
Date:
>
> Jan,
>
> I am dying to take our (your) new procedural language for a spin.  Just one
> problem.   I don't see a Makefile in the pl/plpgsql/src directory.   Am I
> missing something in my configure?
>
> Jan Wieck wrote:
>
> >     Take a look at PL/pgSQL and it's docs which is already in the
> >     CVS and will be shipped with 6.4.
> >
>
>
>

    Don't know where we lost it. Appended at the end.

    Bruce, could you please put it into src/pl/pgsql/src too?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

begin 644 Makefile
M(RTM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T*(PHC($UA:V5F:6QE"B,@
M("`@36%K969I;&4@9F]R('1H92!P;'!G<W%L('-H87)E9"!O8FIE8W0*(PHC
M($E$14Y4249)0T%424]."B,@("`@)$AE861E<CH@+W5S<B]L;V-A;"]S<F,O
M<&=S<6PO<W)C+W!L+W!L<&=S<6PO<W)C+U)#4R]-86ME9FEL92QV(#$N,B`Q
M.3DX+S`X+S$S(#$V.C4S.C0X('!G<W%L($5X<"!P9W-Q;"`D"B,*(RTM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T*"B,*(R!496QL(&UA:V4@=VAE<F4@
M=&AE('!O<W1G<F5S<6P@<V]U<F-E<R!L:79E"B,*4U)#1$E2/2`N+B\N+B\N
M+@H*(PHC($EN8VQU9&4@=&AE(&=L;V)A;"!A;F0@<&]R="!S<&5C:69I8R!-
M86ME9FEL97,*(PII;F-L=61E("0H4U)#1$E2*2]-86ME9FEL92YG;&]B86P*
M:6YC;'5D92`D*%-20T1)4BDO36%K969I;&4N<&]R=`H*0T9,04=3*ST@+4DD
M*$Q)0E!11$E2*2`M220H4U)#1$E2*2]I;F-L=61E"DQ&3$%'4RL]("UI("UL
M"@HC($9O<B!F;6=R+F@*0T9,04=3*ST@+4DD*%-20T1)4BDO8F%C:V5N9`H*
M3$1!1$0K/2`M3"0H3$E"4%%$25(I("UL<'$*"B,*(R!$3$]"2B!I<R!T:&4@
M9'EN86UI8V%L;'DM;&]A9&5D(&]B:F5C="!F:6QE+@HC"D1,3T)*/2!P;'!G
M<W%L)"A$3%-51D9)6"D*"D]"2E,]"7!L7W!A<G-E+F\@<&Q?:&%N9&QE<BYO
M('!L7V-O;7`N;R!P;%]E>&5C+F\@<&Q?9G5N8W,N;PH*04Q,/0DD*$1,3T)*
M*0H*(PHC($)U:6QD('1H92!S:&%R960@;V)J96-T"B,*86QL.B`D*$%,3"D*
M"B0H1$Q/0DHI.@DD*$]"2E,I"@HC"B,@0VQE86X@"B,*8VQE86XZ"@ER;2`M
M9B`D*$%,3"D*"7)M("UF("HN;R!Y+G1A8BYH('!L+G1A8BYH('!L7V=R86TN
M8R!G<F%M+F,@<&Q?<V-A;BYC('-C86XN8PH*:6YS=&%L;#H@86QL"@DD*$E.
M4U1!3$PI("0H24Y35$Q?3$E"7T]05%,I("0H1$Q/0DHI("0H1$535$1)4BDD
M*$Q)0D1)4BDO)"A$3$]"2BD*"B0H1$Q/0DHI.@DD*$]"2E,I"@DD*$-#*2`M
M<VAA<F5D("UO("1`("0H3T)*4RD*"B,))"A,1"D@)"A,1$9,04=37U-,*2`M
M;R`D0"`D*$]"2E,I"@H*<&Q?:&%N9&QE<BYO.@EP;%]H86YD;&5R+F,@<&QP
M9W-Q;"YH('!L+G1A8BYH"@IP;%]C;VUP+F\Z"7!L7V-O;7`N8R!P;'!G<W%L
M+F@@<&PN=&%B+F@*"G!L7V5X96,N;SH)<&Q?97AE8RYC('!L<&=S<6PN:"!P
M;"YT86(N:`H*<&Q?9G5N8W,N;SH)<&Q?9G5N8W,N8R!P;'!G<W%L+F@@<&PN
M=&%B+F@*"G!L7W!A<G-E+F\Z"7!L7V=R86TN8R!P;%]S8V%N+F,@<&QP9W-Q
M;"YH"@DD*$-#*2`D*$-&3$%'4RD@+6,@+6\@)$`@<&Q?9W)A;2YC"@IP;%]G
M<F%M+F,Z"6=R86TN8PH)<V5D("UE("=S+WEY+W!L<&=S<6Q?>7DO9R<@+64@
M)W,O65DO4$Q01U-13%]962]G)R`\9W)A;2YC(#YP;%]G<F%M+F,*"7-E9"`M
M92`G<R]Y>2]P;'!G<W%L7WEY+V<G("UE("=S+UE9+U!,4$=344Q?65DO9R<@
M/'DN=&%B+F@@/G!L+G1A8BYH"@IP;%]S8V%N+F,Z"7-C86XN8PH)<V5D("UE
M("=S+WEY+W!L<&=S<6Q?>7DO9R<@+64@)W,O65DO4$Q01U-13%]962]G)R`\
M<V-A;BYC(#YP;%]S8V%N+F,*"F=R86TN8SH)"6=R86TN>0H*<V-A;BYC.@D)
<<V-A;BYL"@IP;"YT86(N:#H)<&Q?9W)A;2YC"@``
`
end

Re: PL/pgSQL Makefile (was: Re: [HACKERS] SQL Triggers)

From
Bruce Momjian
Date:
Done.


> >
> > Jan,
> >
> > I am dying to take our (your) new procedural language for a spin.  Just one
> > problem.   I don't see a Makefile in the pl/plpgsql/src directory.   Am I
> > missing something in my configure?
> >
> > Jan Wieck wrote:
> >
> > >     Take a look at PL/pgSQL and it's docs which is already in the
> > >     CVS and will be shipped with 6.4.
> > >
> >
> >
> >
>
>     Don't know where we lost it. Appended at the end.
>
>     Bruce, could you please put it into src/pl/pgsql/src too?
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
> begin 644 Makefile
> M(RTM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
> M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T*(PHC($UA:V5F:6QE"B,@
> M("`@36%K969I;&4@9F]R('1H92!P;'!G<W%L('-H87)E9"!O8FIE8W0*(PHC
> M($E$14Y4249)0T%424]."B,@("`@)$AE861E<CH@+W5S<B]L;V-A;"]S<F,O
> M<&=S<6PO<W)C+W!L+W!L<&=S<6PO<W)C+U)#4R]-86ME9FEL92QV(#$N,B`Q
> M.3DX+S`X+S$S(#$V.C4S.C0X('!G<W%L($5X<"!P9W-Q;"`D"B,*(RTM+2TM
> M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
> M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T*"B,*(R!496QL(&UA:V4@=VAE<F4@
> M=&AE('!O<W1G<F5S<6P@<V]U<F-E<R!L:79E"B,*4U)#1$E2/2`N+B\N+B\N
> M+@H*(PHC($EN8VQU9&4@=&AE(&=L;V)A;"!A;F0@<&]R="!S<&5C:69I8R!-
> M86ME9FEL97,*(PII;F-L=61E("0H4U)#1$E2*2]-86ME9FEL92YG;&]B86P*
> M:6YC;'5D92`D*%-20T1)4BDO36%K969I;&4N<&]R=`H*0T9,04=3*ST@+4DD
> M*$Q)0E!11$E2*2`M220H4U)#1$E2*2]I;F-L=61E"DQ&3$%'4RL]("UI("UL
> M"@HC($9O<B!F;6=R+F@*0T9,04=3*ST@+4DD*%-20T1)4BDO8F%C:V5N9`H*
> M3$1!1$0K/2`M3"0H3$E"4%%$25(I("UL<'$*"B,*(R!$3$]"2B!I<R!T:&4@
> M9'EN86UI8V%L;'DM;&]A9&5D(&]B:F5C="!F:6QE+@HC"D1,3T)*/2!P;'!G
> M<W%L)"A$3%-51D9)6"D*"D]"2E,]"7!L7W!A<G-E+F\@<&Q?:&%N9&QE<BYO
> M('!L7V-O;7`N;R!P;%]E>&5C+F\@<&Q?9G5N8W,N;PH*04Q,/0DD*$1,3T)*
> M*0H*(PHC($)U:6QD('1H92!S:&%R960@;V)J96-T"B,*86QL.B`D*$%,3"D*
> M"B0H1$Q/0DHI.@DD*$]"2E,I"@HC"B,@0VQE86X@"B,*8VQE86XZ"@ER;2`M
> M9B`D*$%,3"D*"7)M("UF("HN;R!Y+G1A8BYH('!L+G1A8BYH('!L7V=R86TN
> M8R!G<F%M+F,@<&Q?<V-A;BYC('-C86XN8PH*:6YS=&%L;#H@86QL"@DD*$E.
> M4U1!3$PI("0H24Y35$Q?3$E"7T]05%,I("0H1$Q/0DHI("0H1$535$1)4BDD
> M*$Q)0D1)4BDO)"A$3$]"2BD*"B0H1$Q/0DHI.@DD*$]"2E,I"@DD*$-#*2`M
> M<VAA<F5D("UO("1`("0H3T)*4RD*"B,))"A,1"D@)"A,1$9,04=37U-,*2`M
> M;R`D0"`D*$]"2E,I"@H*<&Q?:&%N9&QE<BYO.@EP;%]H86YD;&5R+F,@<&QP
> M9W-Q;"YH('!L+G1A8BYH"@IP;%]C;VUP+F\Z"7!L7V-O;7`N8R!P;'!G<W%L
> M+F@@<&PN=&%B+F@*"G!L7V5X96,N;SH)<&Q?97AE8RYC('!L<&=S<6PN:"!P
> M;"YT86(N:`H*<&Q?9G5N8W,N;SH)<&Q?9G5N8W,N8R!P;'!G<W%L+F@@<&PN
> M=&%B+F@*"G!L7W!A<G-E+F\Z"7!L7V=R86TN8R!P;%]S8V%N+F,@<&QP9W-Q
> M;"YH"@DD*$-#*2`D*$-&3$%'4RD@+6,@+6\@)$`@<&Q?9W)A;2YC"@IP;%]G
> M<F%M+F,Z"6=R86TN8PH)<V5D("UE("=S+WEY+W!L<&=S<6Q?>7DO9R<@+64@
> M)W,O65DO4$Q01U-13%]962]G)R`\9W)A;2YC(#YP;%]G<F%M+F,*"7-E9"`M
> M92`G<R]Y>2]P;'!G<W%L7WEY+V<G("UE("=S+UE9+U!,4$=344Q?65DO9R<@
> M/'DN=&%B+F@@/G!L+G1A8BYH"@IP;%]S8V%N+F,Z"7-C86XN8PH)<V5D("UE
> M("=S+WEY+W!L<&=S<6Q?>7DO9R<@+64@)W,O65DO4$Q01U-13%]962]G)R`\
> M<V-A;BYC(#YP;%]S8V%N+F,*"F=R86TN8SH)"6=R86TN>0H*<V-A;BYC.@D)
> <<V-A;BYL"@IP;"YT86(N:#H)<&Q?9W)A;2YC"@``
> `
> end
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] SQL Triggers

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
Thus spake Jan Wieck
> > darcy=> CREATE FUNCTION function_dead(int)
> > darcy->     RETURNS int
> > darcy->     AS 'UPDATE bid SET live = \'f\' WHERE item_id = $1;
> > darcy'>         SELECT 1 AS ignore_this'
> > darcy->     LANGUAGE 'sql';
> > CREATE
> > darcy=> CREATE TRIGGER trigger_dead BEFORE INSERT OR UPDATE
> > darcy->     ON bid
> > darcy->     FOR EACH ROW
> > darcy->     EXECUTE PROCEDURE function_dead (item_id);
> > ERROR:  CreateTrigger: function function_dead () does not exist
> > darcy=> SELECT function_dead(1);
> > function_dead
> > -------------
> >             1
> > (1 row)
>
>     The  arguments  aren't right and the return type too. Trigger
>     procedures take no arguments in their definition  and  return
>     OPAQUE type. They really return a tuple of the table they are
>     actually fired for.

Not sure I follow.  The argument is an int (or int4 as it defaults to)
and I tried various combos of int and int4 in the procedure definition

As for the opaque type return, I get an error message when I try to
create an SQL function returning opaque.  The "SELECT 1 AS ignore_this"
is taken straight from the examples.

>     Take a look at PL/pgSQL and it's docs which is already in the
>     CVS and will be shipped with 6.4.

I have the current sources (I sup daily) but I couldn't find any examples
of fcreate trigger.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Re: [HACKERS] SQL Triggers

From
jwieck@debis.com (Jan Wieck)
Date:
> >     The  arguments  aren't right and the return type too. Trigger
> >     procedures take no arguments in their definition  and  return
> >     OPAQUE type. They really return a tuple of the table they are
> >     actually fired for.
>
> Not sure I follow.  The argument is an int (or int4 as it defaults to)
> and I tried various combos of int and int4 in the procedure definition
>
> As for the opaque type return, I get an error message when I try to
> create an SQL function returning opaque.  The "SELECT 1 AS ignore_this"
> is taken straight from the examples.

    To clearify on that:

    Trigger  procedures  cannot  be  created  in  the builtin SQL
    language.   The  trigger  manager  calls  trigger  procedures
    totally  different  from  the  process  how SQL functions get
    invoked. They must be written in C or one of  the  procedural
    languages (PL/pgSQL or PL/Tcl).

>
> >     Take a look at PL/pgSQL and it's docs which is already in the
> >     CVS and will be shipped with 6.4.
>
> I have the current sources (I sup daily) but I couldn't find any examples
> of fcreate trigger.

    There are over 800 lines of trigger and function code written
    in PL/pgSQL. You'll find them in .../src/pl/plpgsql/test.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #