Re: Install new perl test function in PostgreSQL

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