Thread: Install new perl test function in PostgreSQL

First you need to install plperl into your database if you haven't already.

createlang -h <host> -p <port> -U <user> plperl <db_name>

-h, -p aren't really needed if you work on the box where postgres is installed.

After that you just insert/install the function into the database where you have
installed plperl and it should work via select <function_name>.

--
Regards,
Jure Kobal

On Tuesday 25 of August 2009 19:01:44 Jignesh Shah wrote:
> Hi,
>
> I have taken below perl example form PostgreSQL documentation Chapter: 40.1
>
> CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
>         if ($_[0] > $_[1]) { return $_[0]; }
>         return $_[1];
> $$ LANGUAGE plperl;
>
> I understood everything but I don't know how to integrate/install this new
> function with PostgreSQL so that when I run "select perl_max(1,2)" query,
> it returns 2. I have PostgreSQL install on one of my UNIX Server and has
> root privileges. Please help me to install this function.
>
> Thanks,
> Jignesh

2009/8/26 Jignesh Shah <jignesh.shah1980@gmail.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@gmail.com>

That was a perfect answer Michael. It worked. Thanks.

On Wed, Aug 26, 2009 at 12:31 PM, Michael Wood <esiotrot@gmail.com> wrote:

2009/8/26 Jignesh Shah <jignesh.shah1980@gmail.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@gmail.com>

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

Jignesh Shah wrote:

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

It is fairly common to modify functions several times before they are
finalised. Using OR REPLACE simply allows you to do it in one step,
instead of DROP FUNCTION... CREATE FUNCTION... every time you change
something.

The OR REPLACE is only an option, you don't have to use it if you don't
want to.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz