PostgreSQL: Perl procedures with PL/pgSQL.

1. Introduction
2. Downloading and installing Perl.
3. Downloading and installing PostgreSQL with Perl.
4. Example perl/sql commands.
5. Setting up the tables and pl/perl procedures for the Insert, Update, and Delete pl/pgsql procedures
6. Insert pl/pgsql procedure
7. Update pl/pgsql procedure
8. Delete pl/pgsql procedure
9. Considerations to explore.
10. Conclusion
11. References

Other Notes

-----------------------------------------------------------------------

I found something pretty neat, that you don't need to compile perl dynamically. I didn't want to recompile perl, because I already had 5.6.1 installed and I had set up so many options manually that the thought of redoing all that work was not very appealing.

You can compile with libperl.a, the code archive instead of the shared object code. When you run configure on postgresql (tested on 7.1.3), just give the argument
--with-libraries=$LIBPERLA
where LIBPERLA for me was /usr/lib/5.6.1/i686-linux-ld/CORE/libperl.a

Then in the PL/Perl makefile just erase the check for a dynamic perl, lines 12-30. This file is located at
$POSTGRESQL_UNPACKED/src/pl/plperl/Makefile.PL
(tested on 7.1.3)

This works because if you read the comments preceding that check in the makefile, it says this:
# Can't build a shared plperl unless libperl is shared too.
# (Actually, it would be enough if code in libperl.a is compiled
# to be position-independent, but that is hard to check for and
# seems pretty unlikely anyway.)

I don't remember if I compiled that code to be position-independent, but I do know that my pl/perl functions work, as I just tested a few out.
----------------------------------------------------------------------

Introduction

I finally decided to get Perl installed into PostgreSQL because PostgreSQL has all the features I like :

1. A real language for stored procedures (PL/pgSQL).
2. Nice handling of large objects.
3. Embedded Perl commands.
4. Is similar to Oracle in many ways, thus making the transition from Oracle to PostgreSQL or vice versa reasonable.
5. Has many advanced features that I desire with a database server.
6. Has a free web book. I am big on free documentation.

The overall process was a pain because of slight adjustments here and there. . Here are the basic steps:

1. Install Perl 5.6.1. Use all the default options except for two changes.
2. Install PostgreSQL after you install Perl.
3. Install Perl into PostgreSQL, and make one fix.

Downloading and installing Perl.

cd /usr/local/src
lynx --source http://www.tcu-inc.com/perl5.6.1.tgz > perl-5.6.1.tgz
tar -zxvf perl-5.6.1.tgz
cd perl-5.6.1
rm -f config.sh Policy.sh
sh Configure

make
make install

Downloading and Installing PostgreSQL with Perl.

Tcl and Perl are options in the procedural languages. You can actually execute Perl and Tcl inside sql commands. Also, you get the standard PL/pgSQL procedural language (which is similar to pl/sql). Here are the steps I used to install PostgreSQL with Perl. Here is a text file with the same information.

#!/usr/bin

PATH=/usr/local/pg711/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pg711/lib
export PGDATA=/usr/local/pg711/data
export PGLIB=/usr/local/pg711/lib
export POSTGRES_HOME=/usr/local/pg711

chmod 755 .profile

Example perl/sql commands.

Since I had you create the database "postgres", all you have to do is enter these two commands starting as the user "root" to get into the psql interface.

su -l postgres
psql

su -l postgres
PATH=/usr/local/pg711/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pg711/lib
export PGDATA=/usr/local/pg711/data
export PGLIB=/usr/local/pg711/lib
export POSTGRES_HOME=/usr/local/pg711
psql

The following function lets you search the data and return a copy of the name if the name contains the text you search for with a case insensitive option.

drop function search_name(employee,text,integer);
CREATE FUNCTION search_name(employee,text,integer) RETURNS text AS '
    my $emp = shift;
    my $Text = shift;
    my $Case = shift;

    if (($Case > 0) && ($emp->{''name''} =~ /\\Q$Text\\E/i)) 
      { return $emp->{''name''}; }
    elsif ($Case > 0) {return "";}
    elsif ($emp->{''name''} =~ /\\Q$Text\\E/) 
       {    return $emp->{''name''}; }
    else { return "";}
' LANGUAGE 'plperl';

insert into EMPLOYEE values ('John Doe',10000,1);
insert into EMPLOYEE values ('Jane Doe',10000,1);
insert into EMPLOYEE values ('Giny Majiny',10000,1);

select name,search_name(employee,'j',0) from employee;
select name,search_name(employee,'j',1) from employee;

select name from employee where search_name(employee,'j',1) = name;
select name from employee where search_name(employee,'j',0) = name;

Setting up the tables and pl/perl procedures for the Insert, Update, and Delete pl/pgsql procedures

1. Create insert, update, and delete stored procedures that will backup all changes to a history table or backup table. This will record everything that happens. Reasonable error checking is required. We could do more error checking, but the stored procedures I created would just get too big.
2. To use a Perl procedures to clean out input being put into the tables. Granted, we could use sql commands, but the perl commands are so much easier for me to read.

Below, I have three tables: jobs, jobs_backup, and contact. I will only create stored procedures for the table 'jobs'. The two perl procedures are only meant to verify that we have valid data to input, and to filter out non-printable characters, and get rid of whitespace. We use pl/pgsql to perform the actual insert, update, and delete commands.

Using this basic method of handling data, you can replicate it for any other table you have.

Some things I have to watch out for is the fact I want unique names for the jobs. I don't want two jobs to have the same name from one recruiter. This gets a little tricky, but it works fine.

Also, I could use a foriegn key restraint so that you cannot have a contact_id in 'jobs' without it existing in 'contact'. The only problem is, we may at some point accidentally delete contact_ids from contact and then things are messed up anyways. The best solution is to add a "active" column to the "jobs" and "contact" tables in which you turn off and on objects. In this way, you never delete unique ids ever.

Insert pl/pgsql procedure

Update pl/pgsql procedure

Delete pl/pgsql procedure

Considerations to explore.

Here is a summary of the things you should consider:

1. Create a foriegn key constraint so that the contact_id of the jobs has to exist in the contact table.
2. Never delete a row in the tables 'contact' and 'job'. Instead, add a column to just inactivate them. Set the name of the column to be 'active' where 0 means inactive and 1 means active.
3. You can combine insert/update procedures into one procedure. If your data is always 100% accurate, then always issue the update procedure, and if the update procedure doesn't find the job, it just inserts it for you. Under some conditions, this can be useful.
4. Anytime you can ad a check for an error, do it. Although my error checks are reasonable, more can be done.
5. Forget using Perl procedures to execute direct sql commands. Just use them to manipulate data.
6. Use pl/pgsql to combine perl procedures with sql commands.
7. You should setup the procedures to rollback in case an insert, update, or delete couldn't work for some unknown reason.
8. I don't know how much memory is used up with perl procedures and I don't know how memory gets freed up when a perl procedure is done executing. Also, I don't know the overhead of executing perl procedures. For my purposes, pl/pgsql procedures are always going to be faster than manually executing sql commands using Perl scripts on the webserver side. Since I am headed in the right direction anyways, I am willing to live with any overhead there is with the perl procedures. Besides, I can probably take very complex sql commands and shrink them down into a few lines of Perl code. If I balance out the proper use of pl/pgsql, standard sql, and pl/perl, I see significant power gain and little drawbacks.

Freeing up permissions in Perl

First, of all, add this method right below the "permit" method in Safe.pm. My Safe.pm was at /usr/local/src/perl-5.6.1/lib/Safe.pm. Changing a module that you did not create means that if you ever update this module, the changes will get wiped. Once more, you MIGHT MESS UP THE PROGRAMMING FROM ONE OF YOUR FRIENDS WHO IS PROGRAMMING ON THAT COMPUTER AS WELL. Again, I am doing some naughty things you should not do.

sub permit_all {
    my $obj = shift;
    $obj->{Mask} = invert_opset full_opset;
}

Third, recompile plperl with some changes.

Make these changes in the file plperl.c. From this

cd /usr/local/src/postgresql-7.1.1/src/pl/plperl
rm -f *.o
make 
make install

See if you can escape to a shell,

drop function ls_bad ();
CREATE FUNCTION  ls_bad () RETURNS text AS '
my @Temp = `ls /tmp`;
my $List = "@Temp";
$List =~ s/\n/ /g;
  return $List;
' LANGUAGE 'plperl';
select ls_bad();

For a whole day, I was trying to figure out how to get DynaLoader to work in pl/perl. Basically, I read documentation about how to embed Perl in C, and it isn't that hard to do. There is even a manpage about it. I kept on running into problems. Lastly, I tried to not use the Safe.pm module altogether, but I didn't get very far. I was so close to compiling Dynaloader into plperl, but I gave up. After blowing off a day, I want someone else to give it a try.

If you can get DynaLoader to work properly with plperl, or more accurately, you find a way to make it so I can load any module I want with plperl, then please let me know. I got to the point where I could load pure pm modules, but not modules which had c components. I would like to be able to load any module whatsoever. I believe we have to stop using Safe.pm to make it easier. Please send email to articles@gnujobs.com. I would be very interested if you succeed!

Please don't do this. I only wanted to show you how you can get around security issues if you really wanted to.

Conclusion

1. I like use Perl to manipulate the data because SQL is such a pain the in butt sometimes (in manipulating data).
2. The combination of Perl and PL/PGSQL can make it so most of the work is done on the database end, which means, you can do less programming on the client end. For example, let us say you have a webserver that connects to a database server. If the database server is handling a lot of the perl work, your perl scripts on the webserver won't be as big. This is true for stored procedures in general anyways. Is isn't a lot nicer to execute one stored procedure than to have Perl scripts on the webserver side executing all those steps (that the procedure does for you)?
3. If you can do all the fancy perl programming on the database end, then you will have less work with any language that you choose to connect to your database server. Thus, your stored procedures become objects that your web programmers just have to understand how to use, but not understand how they were made. This is very nice.
4. I am going to slowly use more perl procedures (where appropriate) and test the stability of pl/perl.
5. I would like to be able to load any module into plperl just for giggles. If you find out how to do with, please send me email at articles@gnujobs.com.

References

1. Procedural Languages
2. PostgreSQL: Introduction and Concepts
3. A recent article, http://www.newbienetwork.net/sections.php?op=viewarticle&artid=25
4. If this article changes, it will be available here http://www.gnujobs.com/Articles/20/Perl_PostgreSQL.html

Mark works as an independent consultant donating time to causes like GNUJobs.com, writing articles, writing free software, and working as a volunteer at eastmont.net.

Copyright © 4/2001 Mark Nielsen
Article