Thread
-
SQL Triggers
D'Arcy Cain <darcy@druid.net> — 1998-09-20T04:45:23Z
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
Jan Wieck <jwieck@debis.com> — 1998-09-24T08:55:50Z
> > 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
David Hartwig <daveh@insightdist.com> — 1998-09-24T14:33:45Z
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)
Jan Wieck <jwieck@debis.com> — 1998-09-25T07:12:02Z
> > 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)
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-09-25T15:50:03Z
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
D'Arcy Cain <darcy@druid.net> — 1998-09-25T17:01:26Z
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
Jan Wieck <jwieck@debis.com> — 1998-09-25T17:52:29Z
> > 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) #