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: Michael Wood <esiotrot(at)gmail(dot)com>
Cc: 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-26 14:42:10
Message-ID: c11950270908260742n1c6e7388t6c28f2adae242751@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

CREATE FUNCTION my_perlfunc (integer, integer) RETURNS VOID
  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.

Jignesh
On Wed, Aug 26, 2009 at 4:07 PM, Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com>wrote:

> That was a perfect answer Michael. It worked. Thanks.
>
>
> On Wed, Aug 26, 2009 at 12:31 PM, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
>
>> 2009/8/26 Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com>:
>> > Thanks Jure. I have already plperl installed because I could see that
>> many
>> > new perl functions have been installed but I don't know where it plperl
>> > installed. Could you tell me if there is any way to find out where it is
>> > installed? Morever, if I get the location plperl install location, how
>> to
>> > insert/install my perl function? Is there any command available for
>> this. My
>> > questions might be silly but this is first time I am using PostgreSQL.
>>
>> If it PL/Perl is installed in your database then you do not need to
>> know "where it is".  It's in your database.  So you can just create
>> your function and it should work.  e.g.:
>>
>> $ psql dbname
>> Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
>>
>> Type:  \copyright for distribution terms
>>       \h for help with SQL commands
>>       \? for help with psql commands
>>       \g or terminate with semicolon to execute query
>>       \q to quit
>>
>> dbname=> SELECT * FROM pg_language;
>>  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid |
>> lanvalidator | lanacl
>>
>> ----------+----------+---------+--------------+---------------+--------------+--------
>>  internal |       10 | f       | f            |             0 |
>> 2246 |
>>  c        |       10 | f       | f            |             0 |
>> 2247 |
>>  sql      |       10 | f       | t            |             0 |
>> 2248 |
>> (3 rows)
>>
>> dbname=> CREATE LANGUAGE plperl;
>> CREATE LANGUAGE
>> dbname=> SELECT * FROM pg_language;
>>  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid |
>> lanvalidator | lanacl
>>
>> ----------+----------+---------+--------------+---------------+--------------+--------
>>  internal |       10 | f       | f            |             0 |
>> 2246 |
>>  c        |       10 | f       | f            |             0 |
>> 2247 |
>>  sql      |       10 | f       | t            |             0 |
>> 2248 |
>>  plperl   |    16386 | t       | t            |         19193 |
>>  19194 |
>> (4 rows)
>>
>> dbname=> CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
>> dbname$>     if ($_[0] > $_[1]) { return $_[0]; }
>> dbname$>     return $_[1];
>> dbname$> $$ LANGUAGE plperl;
>> CREATE FUNCTION
>> dbname=> SELECT perl_max(55, 23);
>>  perl_max
>> ----------
>>       55
>> (1 row)
>>
>> dbname=> SELECT perl_max(55, 97);
>>  perl_max
>> ----------
>>       97
>> (1 row)
>>
>> dbname=>
>>
>> See also:
>> http://www.postgresql.org/docs/8.4/static/xplang.html
>> http://www.postgresql.org/docs/8.4/static/plperl.html
>>
>> --
>> Michael Wood <esiotrot(at)gmail(dot)com>
>>
>
>

In response to

Responses

pgsql-novice by date

Next:From: Jignesh ShahDate: 2009-08-26 14:52:42
Subject: Perl trigger not working
Previous:From: Oliveiros C,Date: 2009-08-26 13:53:21
Subject: Re: How to copy value between tables

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