Thanks I will try it today and get back here in case of any problems. I could see "CREATE OR REPLACE" in every trigger example. I am wondering why we need REPLACE? What is it purpose?
Thanks,
Jignesh
On Thu, Aug 27, 2009 at 1:23 AM, Shane Ambler
<pgsql@sheeky.biz> wrote:
Jignesh Shah wrote:
Michael, I have one question. I have written a perl code for detecting
trigger type(insert, update or delete) and based on that performing the
operation. Now I want to make this code as a trigger. Coud you tell me I
have to copy paste all written lines while creating function for it or I can
create file somewhere and give it as a input? Please let me know if below
are correct way to do it?
want to use \i /path/to/my/triggerfile
The \i command can be used to process any valid sql file for input. CREATE FUNCTION my_perlfunc (integer, integer) RETURNS VOID
Taking no arguments and returning type trigger would be what you are looking for. Check chapter 40.6 for how to access column values.
You will also want to make sure you return "SKIP" or "MODIFY" when
needed. AS *** Large number of lines Perl code ***
LANGUAGE plperl;
>
CREATE TRIGGER my_trigger AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE PROCEDURE my_perlfunc(integer, integer);
Moreover, I have tried to create trigger like below to execute perl_max
function but it gives error. Am I missing something?
mydb=# SELECT perl_max(13,9);
13
mydb=# CREATE TRIGGER my_trigger AFTER INSERT OR UPDATE OR DELETE ON
my_table
mydb-# FOR EACH ROW EXECUTE PROCEDURE perl_max(integer, integer);
ERROR: function perl_max() does not exist
Thanks for being there.
Shane Ambler
pgSQL (at) Sheeky (dot) Biz