Re: Install new perl test function in PostgreSQL

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(at)sheeky(dot)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?
>>
>
> Copy and paste works - if it is extra long then when using psql you may
> 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
>>
>
> CREATE FUNCTION my_perlfunc () RETURNS trigger
>
> 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
>