Re: Install new perl test function in PostgreSQL

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