Skip site navigation (1) Skip section navigation (2)

Re: Install new perl test function in PostgreSQL

From: Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com>
To: Shane Ambler <pgsql(at)sheeky(dot)biz>
Cc: Michael Wood <esiotrot(at)gmail(dot)com>, Jure Kobal <j(dot)kobal(at)gmx(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Install new perl test function in PostgreSQL
Date: 2009-08-27 06:37:25
Message-ID: c11950270908262337w722dfe06ube9c894e5de675a0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Ibrahim ShaameDate: 2009-08-27 06:58:58
Subject: Re: how to install postgresql on windows without admin privileges
Previous:From: Jignesh ShahDate: 2009-08-27 05:18:13
Subject: Re: Perl trigger not working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group