SQL Triggers

Started by Nonameover 27 years ago7 messages

darcy@druid.net

over 27 years ago

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.

Noname

jwieck@debis.com

over 27 years ago

In reply to: Noname (#1)

Re: [HACKERS] SQL Triggers

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) #

David Hartwig

daveh@insightdist.com

over 27 years ago

In reply to: Noname (#2)

Re: [HACKERS] SQL Triggers

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:

Show quoted text

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

Noname

jwieck@debis.com

over 27 years ago

In reply to: David Hartwig (#3)

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

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

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

maillist@candle.pha.pa.us

over 27 years ago

In reply to: Noname (#4)

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

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

Noname

darcy@druid.net

over 27 years ago

In reply to: Noname (#2)

Re: [HACKERS] SQL Triggers

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.

Noname

jwieck@debis.com

over 27 years ago

In reply to: Noname (#6)

Re: [HACKERS] SQL Triggers

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