"Linux Gazette...making Linux just a little more fun!"

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

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.

### First of all, you have to compile Perl as a dynamic module. 
### If you haven't done this, you should be able to install postgresql,
### but it won't have the plperl interface. 

cd /usr/local/src
lynx --source ftp://postgresql.readysetnet.com/pub/postgresql/v7.1.1/postgresql-7.1.1.tar.gz > postgresql-7.1.1.tar.gz
tar -zxvf postgresql-7.1.1.tar.gz
cd postgresql-7.1.1

### We need to set some environment variables -- which should be put
#### into ~/.profile for the user postgres for the future. 

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

  ### This script is setup to delete any previous installation. 
  ### I did this so that I could debug it if it didn't work the first time.

  #### Ignore any error message saying the database server is not running. You 
  ### probably don't have one running.
su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data -l logfile stop' postgres
  ### Ignore any error message saying this user exists.
adduser postgres
rm -rvf /usr/local/pg711

  ### Now let us make the destination directory have postgres own it. 
mkdir /usr/local/pg711 
chown postgres /usr/local/pg711

  ### Ignore any make clean errors here. 
make clean
  ### Compile and install postgresql.
./configure --prefix=/usr/local/pg711 --with-perl --with-tcl --with-CXX --with-python --enable-odbc 
make
make install

  ### Now we need to install the perl interface for postgresql.
gmake -C src/interfaces/perl5 install
cd /usr/local/src/postgresql-7.1.1/src/interfaces/perl5
perl Makefile.PL
make 
  ### Uncomment the next line if you want to test it. 
##  su -c 'make test' postgres
make install

  ### Change ownership of all files to the user postgres.
chown -R postgres /usr/local/pg711

  ### Initialize the database. 
su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data' postgres

  ### Start the database server. 
su -c '/usr/local/pg711/bin/pg_ctl -D /usr/local/pg711/data -l logfile start' postgres

  ### The interfaces for perl, tcl, and pl/pgsql should have been created. 
  ### Now add them. 
  
su -c 'createlang plpgsql template1' postgres
su -c 'createlang pltcl template1' postgres

### Now assuming you have perl 5.6.1 installed correctly.
rm -f /usr/local/pg711/lib/libperl.so
ln -s /usr/lib/perl5/5.6.1/i686-linux/CORE/libperl.so \
  /usr/local/pg711/lib/libperl.so
su -c 'createlang plperl template1' postgres

  ### If it worked out correctly, any new database will copy itself from 
  ### template1 and have perl, tcl, and pl/pgsql. 

  ### Now additional stuff.
su -c 'createdb postgres' postgres

#!/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.

 --- Create the jobs table. 
 --- a good suggestion would be to have a foriegn key constraint
 --- with the table contact.
create sequence Job_Sequence;
drop table jobs;
create table jobs (
job_id int4 unique DEFAULT nextval('Job_Sequence'),
contact_id int4,
job_no int4,
job_name  text,
job_location text 
);
CREATE UNIQUE INDEX job_index ON jobs (job_name, contact_id);

-- This is a real backup table. 
-- Everytime a changes occur, insert it into this table. 
-- This isn't just for deletes, but for inserts and updates. 
-- This becomes a history table, not just a backup.
-- We even record the final output. 
create sequence Backup_Job_Sequence;
drop table jobs_backup;
create table jobs_backup (
backup_id int4 unique DEFAULT nextval('Backup_Job_Sequence'),
action text CHECK (action in ('insert','update','delete','')),
error_code int4,
job_id int4,
contact_id int4,
job_no int4,
job_name  text,
job_location text
);

create sequence Contact_Sequence;
drop table contact;
create table contact (
contact_id int4  UNIQUE DEFAULT nextval('Contact_Sequence'),
name text unique,
phone text,
website text
);

 --- Insert two values for contacts.
 --- I am not making stored procedures for this table, just jobs. 
insert into contact (name,phone,website) 
  values ('Mark Nielsen','(408) 891-6485','http://www.gnujobs.com');
insert into contact (name,phone,website)
  values ('Joe Shmoe','(1234) 111-1111','http://www.gnujobs.net');
insert into contact (name,phone,website)
  values ('Lolix.org','(12345) 111-1111','http://www.lolix.org');


 --- Select info from contact to see if it is there.
select * from contact;

 --- Let use create perl function (which is probably not needed)
 --- which will verify if inputted data in not blank. 

drop function job_values_verify (int4,text,text);
CREATE FUNCTION  job_values_verify (int4,text,text) RETURNS int4 AS '
    my $Contact_Id = shift;
    my $Job_Name = shift;
    my $Job_Description = shift;
    my $Error = 0;
    if ($Contact_Id < 1) {$Error = -100;}
    if (!($Job_Name =~ /[a-z0-9]/i)) {$Error = -101;}
    if (!($Job_Description =~ /[a-z0-9]/i)) {$Error = -102;}
  return $Error;
' LANGUAGE 'plperl';

drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front. 
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end. 
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space. 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up. 
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');
--

Insert pl/pgsql procedure

drop function insert_job (int4,text,text);
CREATE FUNCTION insert_job (int4,text,text) RETURNS int2 AS '
DECLARE
    c_id_ins int4; j_name_ins text;  l_ins text; 
    job_id1 int4; oid1 int4; test_id int4 := 0; j_no_ins int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD;
BEGIN
   j_name_ins := $2; l_ins  := $3; c_id_ins := $1;

     -- We execute a few Perl procedures now. These are just examples
     -- of Perl procedures.
     -- Clean the name of the job.
   SELECT INTO record4 clean_text(j_name_ins) as text1;
   j_name_ins = record4.text1;
     -- Clean the location of the job.
   SELECT INTO record4 clean_text(l_ins) as text1;
   l_ins = record4.text1;
     -- Verify the values we insert are okay.
   SELECT INTO record4 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
   IF record4.no < 0 THEN return (record3.no); END IF;

     -- See if we have unique names, otherwise return 0.
   FOR record1 IN SELECT job_id FROM jobs  
      where contact_id = c_id_ins and job_name = j_name_ins
      LOOP
      test_id := record1.job_id;
   END LOOP;
     -- If the job_id is null, great, otherwise abort and return -1;
   IF test_id > 0 THEN return (-1); END IF;

   FOR record3 IN SELECT max(job_no) from jobs_backup where contact_id = c_id_ins
      LOOP
      IF record3.max IS NULL THEN j_no_ins := 0; END IF;
      IF record3.max > -1 THEN j_no_ins = record3.max + 1; END IF;
   END LOOP;

     -- Insert the stuff. Let the sequence determine the job_id.
   insert into jobs (contact_id, job_no, job_name, job_location)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins);
     -- Get the unique oid of the row just inserted. 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the job id. Do not use SELECT INTO, since record2 needs to be assigned.
   FOR record2 IN SELECT job_id FROM jobs where oid = oid1
      LOOP
      job_id1 := record2.job_id;
   END LOOP;
   
     -- If job_id1 is NULL, insert failed or something is wrong.
   IF job_id1 is NULL THEN return (-2); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF job_id1 < 1 THEN return (-3); END IF;

     -- Everything has passed, return job_id1 as job_id.
   insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins, ''insert'', job_id1);
   return (job_id1);
END;
' LANGUAGE 'plpgsql';
select insert_job (1,'Job Title 1','Boston, MA');
select insert_job (1,'Job Title 2','San Jose, CA');
select insert_job (2,'Job Title 1','Columbus, Ohio');
select insert_job (2,'Job Title 2','Houston, TX');
select insert_job (3,'Job Title 1','Denver, CO');
select insert_job (3,'Job Title 2','New York, NT');
select * from jobs;

Update pl/pgsql procedure

drop function update_job (int4,text,text,int4);
CREATE FUNCTION update_job (int4,text,text,int4) RETURNS int2 AS '
DECLARE
    c_id_ins int4; j_name_ins text;  l_ins text; 
    job_id1 ALIAS FOR $4; oid1 int4; test_id int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD; record5 RECORD;  
    return_int4 int4 := 0; job_no1 int4 := 0;
BEGIN
   j_name_ins := $2; l_ins  := $3; c_id_ins := $1;

     -- A few Perl procedures. 
     -- Clean the name of the job.
   SELECT INTO record4 clean_text(j_name_ins) as text1;
   j_name_ins = record4.text1;
     -- Clean the location of the job. 
   SELECT INTO record5 clean_text(l_ins) as text1;
   l_ins = record5.text1;
     -- Verify the values we insert are okay.
   SELECT INTO record3 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
   IF record3.no < 0 THEN return (record3.no); END IF;

     -- See if there is a duplicate job name for that contact.
   FOR record1 IN SELECT job_id FROM jobs  
      where contact_id = c_id_ins and job_name = j_name_ins
        and job_id != job_id1
      LOOP
      test_id := record1.job_id;
   END LOOP;
     -- If the job_id is null, great, otherwise abort and return -1;
   IF test_id > 0 THEN return (-1); END IF;

     -- See if the job exists, otherwise return -2.
   FOR record1 IN SELECT * FROM jobs where job_id = job_id1  
      LOOP
      update jobs set contact_id = c_id_ins,  
        job_name = j_name_ins, job_location = l_ins
        where job_id = job_id1;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      test_id := 1;
      job_no1 := record1.job_no;
   END LOOP;

     -- If the job does not exist, what are we updating? return error. 
   IF test_id = 0 THEN return (-2); END IF;

     -- Everything has passed, return return_int4.
   insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)
        values (c_id_ins, job_no1, j_name_ins, l_ins, ''update'', return_int4, job_id1);
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
select update_job (3,'Changing title and owner.','Boston, MA',1);
select * from jobs;
  -- You should get an error on this one because you are duplicating name
  -- and contact id. 
select update_job (3,'Changing title and owner.','Boston, MA',1);

Delete pl/pgsql procedure

drop function delete_job (int4);
CREATE FUNCTION delete_job (int4) RETURNS int2 AS '
DECLARE
    job_id1 ALIAS FOR $1;
    job_exists int4 := 0;
    job_backup_exists int4 := 0;
    record1 RECORD; 
    return_int4 int4 :=0;
BEGIN
     -- If the job_id1 is not greater than 0, return error.
   IF job_id1 < 1 THEN return -1; END IF;

     -- If we find the job, delete it, record we found it, and back it up. 
     -- I do not like using LOOP for one row, but I use it for a reason.
   FOR record1 IN SELECT * FROM jobs where job_id = job_id1
      LOOP
      delete from jobs where job_id = job_id1;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      job_exists := 1;
      insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)
        values (record1.contact_id, record1.job_no, record1.job_name, 
          record1.job_location, ''delete'', return_int4, record1.job_id);
   END LOOP;

     -- If job_exists == 0, Return error.
     -- It means it never existed. 
   IF job_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
select delete_job (1);
select * from jobs;
  --- We already deleted it, we should get an error this time. 
select delete_job (1);

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

                "require Safe; SPI::bootstrap();"
                "sub ::mksafefunc { my $x = new Safe; $x->permit_only(':default');$x->permit(':base_math');"
                "$x->share(qw[&elog &DEBUG &NOTICE &NOIND &ERROR]);"
                " return $x->reval(qq[sub { $_[0] }]); }"

                "require Safe; SPI::bootstrap();"
                "sub ::mksafefunc { my $x = new Safe; $x->permit_only(':default');$x->permit(':base_math');"
 "$x->permit_all('');"
                "$x->share(qw[&elog &DEBUG &NOTICE &NOIND &ERROR]);"
                " return $x->reval(qq[sub { $_[0] }]); }"

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 . 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 (or any other language -- should someone make a Python interface?) 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 .

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 Nielsen

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 © 2001, Mark Nielsen.
Copying license http://www.linuxgazette.com/copying.html
Published in Issue 67 of Linux Gazette, June 2001

---