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

Combining Perl and PostgreSQL, Part 2: Procedures with PL/pgSQL

By Mark Nielsen

Introduction
Perl script to create tables, procedures, backup tables, and sequences.
Executing the Perl script
Considerations to explore.
Conclusion
References

Introduction

After dealing with installing PostgreSQL, Perl, and embedding Perl in PostgreSQL, I wanted a standard way to create tables, sequences, stored procedures, and backup tables. Perhaps other people have nice GUI solutions to do this, but I haven't seen any. I would like it if someone would work with me to create a GUI interface to achieve what I am doing here. My goals are:

Always backup data no matter what happens.
Always use stored procedures to insert, update, delete, copy, or to do anything that changes data on the tables. One should even create stored procedures to select data.
Have a Perl script create tables, sequences, backups tables, and the stored procedures to manipulate the data.
Have the stored procedures clean data using Perl.
Backup data if someone runs the Perl script on a live system.
There should be an active column in the table so that you can specify active or inactive rows in the table. We create a view which views active rows of a table.
Stored procedures should record date created and date last updated.
All rows have a unique id. Even if we choose not to use them, they will still have them. It is not always good to use oid to get unique rows.
Be able to delete inactive rows with a purge procedure. The delete procedure just makes it inactive. Also, unpurge data with the latest purged data for a unique id. This is cool.
All negative numbers returned from pl/sql procedures are considered failures. All positive numbers (including 0) are considered to be successes in the fact nothing errored out. They are either 0, in which nothing happened, or something greater than 0 which indicates the number of items affected or a id number.

My future goals include:

Creating a GUI interface. Preferrably one that is not dependent on GNOME or KDE libraries but Python. You can create Python binaries easily, so I would prefer Python/TK.
Allow the GUI design to make changes to live tables by either:
- Making updates that really happen with full effects. Some changes con't allow all options (at least in the past).
- Creating a new table, and dumping all the data from the old table into the new one while locking the old table.
Record all database changes to review history.

Perl script to create tables, procedures, backup tables, and sequences.

Here is the Perl script I use. You can also get a copy here Create_Functions.pl.txt.

#!/usr/bin/perl

#              Create Functions for Perl/PostgreSQL version 0.1

#                       Copyright 2001, Mark Nielsen
#                            All rights reserved.
#    This Copyright notice was copied and modified from the Perl 
#    Copyright notice. 
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of either:

#        a) the GNU General Public License as published by the Free
#        Software Foundation; either version 1, or (at your option) any
#        later version, or

#        b) the "Artistic License" which comes with this Kit.

#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
#    the GNU General Public License or the Artistic License for more details.

#    You should have received a copy of the Artistic License with this
#    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

#    You should also have received a copy of the GNU General Public License
#   along with this program in the file named "Copying". If not, write to the 
#   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
#    02111-1307, USA or visit their web page on the internet at
#    http://www.gnu.org/copyleft/gpl.html.

use strict;

  ### We want to define some variables WHICH YOU SHOULD CHANGE FOR YOUR
  ### OWN COMPUTER. 
my $Home = "/tmp/testdir";
my $File = "$Home/Tables.txt";
my $Template = "$Home/Generic.fun";
my $Custom = "$Home/Custom.sql";
my $Database = "testdatabase";
 
#------------------------------------------------------------------------

my @List = @ARGV;

  ## Let us create the two directories we need if they are not there. 
if (!(-e "$Home/Tables")) {system "mkdir -p $Home/Tables"}
if (!(-e "$Home/Backups")) {system "mkdir -p $Home/Backups"}

  ### Open up the template for the functions and the file that contains
  ### the info to create the tables. 
open(FILE,$Template); my @Template = <FILE>; close FILE;
open(FILE,$File); my @File = <FILE>; close FILE;
open(FILE,$Custom); my @Custom = <FILE>; close FILE;

  ### Filter out lines that have no numbers or letters. 
@File = grep($_ =~ /[a-z0-9]/i, @File);
  ### Get rid of any line which contains a #
@File = grep(!($_ =~ /\#/), @File);
  ### Get rid of the newline. 
grep(chomp $_, @File);
  ### Get rid of tabs and replace with spaces. . 
grep($_ =~ s/\t/ /g, @File);
  ### Convert all multiple spaces to one.  
grep($_ =~ s/  +/ /g, @File);
  ### Next two lines get rid of spaces and front and end.  
grep($_ =~ s/^ //g, @File);
grep($_ =~ s/ $//g, @File);
  ### Delete any commas at the end, we will put them back on later. 
grep($_ =~ s/\,$//g, @File);

my $Tables = {};
my $TableName = "";
  ### For each line in the file, either make a new array for the table, 
  ### or store the lines in the array for a table.  
foreach my $Line (@File)  
  {
  my $Junk = "";
    ### If the line starts with "TABLENAME" then create a new array. 
  if ($Line =~ /^TABLENAME/) 
    {
    ($Junk,$TableName, $Junk) = split(/ /,$Line);
       ### This creates the aray for the table. 
    $Tables->{$TableName} = [];
    }
  else 
    {
       ### Storing lines for the table. 
    push (@{$Tables->{$TableName}}, $Line) ;
    }
  }

    ### If we listed specific tables, then only do those. 
  if (@List) 
    {
    foreach my $TableName (sort keys %$Tables)
      { if (!(grep($_ eq $TableName, @List))) {delete $Tables->{$TableName};} }
    }

  ### Get the keys of the reference to an array $Tables
  ### and get the data for that array, create our file, and then use the file. 
foreach my $TableName (sort keys %$Tables) 
  {
  my @Temp = @{$Tables->{$TableName}};

  my $Backup_Columns = "";  my $Backup_Values = ""; my $Update_Fields = "";
  my $Field_Copy_Values = "";  my $FieldTypes = "";
  my $CleanVariables = ""; my $RemakeVariables = ""; 
    ### The two tables are different in one respect, the backup table 
    ### does not require uniqueness and it doesn't use a sequence.  
  my $Table = qq($TableName\_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
);
    ## I should allow null for the id instead of 0, but since the sequence
    ### starts at 1, I use 0 as null. I hate nulls.  
  my $Table_Backup = qq(backup_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence_backup'), 
    $TableName\_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    );

  print "Creating functions for table '$TableName'\n";
  my $No = 1;
    ### For each line for this table do this. 
    ### We want to create a few variables that are going to be placed into
    ### the template.  
  foreach my $Line (@Temp) 
    {
    $Table .= "$Line,\n";
    $Table_Backup .= "$Line,\n";
    my ($Name,$Type,$Ext) = split(/ /,$Line,3);
      ### The backup columns
    $Backup_Columns .= ", $Name"; 
      ### The update fields
    $No++; $Update_Fields .= ", $Name = var_$No"; 
      ### Backup values
    $Backup_Values .= ", record_backup.$Name";
      ### Now the fields when we copy stuff in the cyop function. 
    $Field_Copy_Values .= ", clean_text(record2.$Name)";
      ### Now the field types for the update function. 
    $FieldTypes .= ", $Type";
      ### We need to define the variables for the updating function.
    $CleanVariables .= "          var_$No $Type;\n";
      ### We need to define the type, I only check for text and int4 for now. 
    my $Temp = "\$$No";  
    if ($Type eq "int4") {$Temp = "clean_numeric($Temp)";}
    elsif  ($Type eq "text") {$Temp = "clean_text($Temp)";}
      ### Now we need to set the variables. 
    $RemakeVariables .= "         var_$No := $Temp;\n";

     ### We also need to add the function to clean out he variables before
     ### they are submitted. 
    }
   ### Record how many rows we had. Make a line for the update command for
   ### testing. 
  my $Number_Of_Rows = $No;
  my $Update_Test = "1";
  for (my $i = 1; $i < $Number_Of_Rows - 1; $i++) {$Update_Test .= ",$i";}

    ### We need to chop off the last comma. 
  chomp $Table; chop $Table; chomp $Table_Backup; chop $Table_Backup;
    ### Now let us setup dropping and creating of the table and backup table. 
  my $Tables = qq(drop table $TableName;\ncreate table $TableName (\n$Table\n);); 
  $Tables .= "drop table $TableName\_backup;\n";
  $Tables .= "create table $TableName\_backup (\n$Table_Backup, error_code text NOT NULL DEFAULT ''\n);\n";
    ### Let us create a view for active stuff in our table. 
  $Tables .= "drop view $TableName\_active;\n";
  $Tables .= "create view $TableName\_active as select * from $TableName
        where active = 1;\n";
    ### Create a view for inactive or deleted items. 
  $Tables .= "drop view $TableName\_deleted;\n";
  $Tables .= "create view $TableName\_deleted as select * from $TableName
        where active = 0;\n";
    ### Create a view for a list of unique backup ids. 
  $Tables .= "drop view $TableName\_backup_ids;\n";
  $Tables .= "create view $TableName\_backup_ids as 
           select distinct $TableName\_id from $TableName\_backup;\n";
    ### Create a list of purged data (lastest data per id).  
  $Tables .= "drop view $TableName\_purged;\n";
  $Tables .= "create view $TableName\_purged as
   select * from $TableName\_backup where oid = ANY (
     select max(oid) from $TableName\_backup where $TableName\_id = ANY
        (
        select distinct $TableName\_id from $TableName\_backup
          where $TableName\_backup.error_code = 'purge'
           and NOT $TableName\_id = ANY (select $TableName\_id from $TableName)
        )
        group by $TableName\_id
     )
    ;\n";

     ### I use grep commands to search and replace stuff for arrays.
     ### I could use map, but I like greps.  
  my @Temp = @Template;
     ### now add the custom sql commands. 
  push (@Temp,@Custom);

  grep($_ =~ s/TABLENAME/$TableName/g, @Temp);
  grep($_ =~ s/BACKUPCOLUMNS/$Backup_Columns/g, @Temp);
  grep($_ =~ s/BACKUPVALUES/$Backup_Values/g, @Temp);
  grep($_ =~ s/UPDATEFIELDS/$Update_Fields/g, @Temp);
  grep($_ =~ s/COPYFIELDS/$Field_Copy_Values/g, @Temp);
  grep($_ =~ s/FIELDS/$FieldTypes/g, @Temp);
  grep($_ =~ s/HOME/$Home/g, @Temp);
  grep($_ =~ s/CLEANVARIABLES/$CleanVariables/g, @Temp);
  grep($_ =~ s/REMAKEVARIABLES/$RemakeVariables/g, @Temp);

    ### Now move the stuff from the array @Temp to @Template_Copy.
  my @Template_Copy = @Temp;

    ### Now we save the file. We won't delete it (unless you run this script
    ### again) so that we can figure out what was done.  
  open(FILE,">$Home/Tables/$TableName\.table_functions");
    ### Create the sequence for the table . 
  print FILE "drop sequence $TableName\_sequence;\n";
  print FILE "create sequence $TableName\_sequence;\n";
  print FILE "drop sequence $TableName\_sequence_backup;\n";
  print FILE "create sequence $TableName\_sequence_backup;\n";
    ### Print out the table and backup table.   
  print FILE $Tables;
    ### Print out the 4 functions, insert, delete, update, and copy. 
  foreach my $Temp (@Template_Copy) {print FILE "$Temp";} 

  close FILE;

    ### Before we execute, let us backup the table in case some novice 
    ### executes this on a live server. 
  my $Backup_File = "$Home/Backups/$TableName\_0.backup";
  my $No = 0;
  while (-e $Backup_File)
    {$No++; $Backup_File = "$Home/Backups/$TableName\_$No\.backup";} 
    ### Now we have the filename to store the backup, execute it. 
  system ("pg_dump -t $TableName -f $Backup_File $Database");
  
  ### Uncomment this option if you want to see what is in the file. 
##  system ("cat $Home/Tables/$TableName\.table_functions");

    ### Drop table and functions, create table and functions and backup table. 
  system ("psql -d $Database -c '\\i $Home/Tables/$TableName\.table_functions'");
  print "Check the file\n $Home/Tables/$TableName\.table_functions.\n";

  }

Rename the perl script "Create_Functions.pl.txt". Here are the things needed to get it to work:

You must change the options above the dotted line.
You must have PostgreSQL installed with Perl embedded in it.
Enter the command "psql template1". Then type "create database testdatabase;" or whatever you named your database. Press enter. If you have errors along the way, you haven't set up the permissions yet. Login in as root, then execute "su -l postgres". Then type "createuser" and press enter. This will create a user in your postgresql database. Enter in the username and give the username full privledges. Then try again with your normal account.

Executing the Perl script

You will need some more files. The Tables.txt file.


TABLENAME contact
question_id int4 NOT NULL DEFAULT 0
company_name text NOT NULL default ''
first  text NOT NULL default ''
middle text NOT NULL default ''
last text NOT NULL default ''
email  text NOT NULL default ''
work_phone text NOT NULL default ''
home_phone text NOT NULL default ''
address_1 text NOT NULL default '',
address_2 text NOT NULL default ''
city text NOT NULL default ''
state text NOT NULL default ''
zip text NOT NULL default ''

TABLENAME account
username text NOT NULL DEFAULT '',
password text not NULL DEFAULT '',

TABLENAME contact_lists
account_id int4 not null default 0,
contact_id int4 not null default 0,

You can use my file as an example, but I suggest to modify it for your own needs. It is simulated to make three tables. One containing userame and passwords, and the other associating a username to a list of contacts. Another file you will need is Generic.fun

---              Generic Functions for Perl/Postgresql version 0.1

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.  
-- create a method to purge one item. 
--  \i HOME/TABLENAME.table
---------------------------------------------------------------------

drop function sql_TABLENAME_insert ();
CREATE FUNCTION sql_TABLENAME_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into TABLENAME (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted. 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the TABLENAME id. 
   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where oid = oid1
      LOOP
      id := record1.TABLENAME_id;
   END LOOP;
   
     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id < 1 THEN return (-2); END IF;

      -- Now backup the data. 
    FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
       LOOP
       insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created, 
           active, error_code) 
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as TABLENAME_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_TABLENAME_delete (int4);
CREATE FUNCTION sql_TABLENAME_delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 0. 
   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME 
          where TABLENAME_id = id
      LOOP
      update TABLENAME set active=0, date_updated = CURRENT_TIMESTAMP
           where TABLENAME_id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP
      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''delete''
      );
   END LOOP;

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

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_TABLENAME_update (int4 FIELDS);
CREATE FUNCTION sql_TABLENAME_update  (int4 FIELDS) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
    CLEANVARIABLES
BEGIN
    REMAKEVARIABLES
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record_update IN SELECT TABLENAME_id FROM TABLENAME
         where TABLENAME_id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update TABLENAME set date_updated = CURRENT_TIMESTAMP
      UPDATEFIELDS 
        where TABLENAME_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP
     insert into TABLENAME_backup (TABLENAME_id,
         date_updated, date_created, active
         BACKUPCOLUMNS, error_code)
       values (record_update.TABLENAME_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         BACKUPVALUES, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_TABLENAME_copy (int4);
CREATE FUNCTION sql_TABLENAME_copy (int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;    
    return_int4 int4 := 0;
    id_new int4 := 0;
    TABLENAME_new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where TABLENAME_id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql_TABLENAME_insert() as TABLENAME_insert
      LOOP
      TABLENAME_new := record1.TABLENAME_insert;
   END LOOP;
     -- If the TABLENAME_new is not greater than 0, return error.
   IF TABLENAME_new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM TABLENAME where TABLENAME_id = id
      LOOP

     FOR record1 IN SELECT sql_TABLENAME_update(TABLENAME_new COPYFIELDS)
        as TABLENAME_insert
      LOOP
        -- execute some arbitrary command just to get it to pass. 
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.   
   return (TABLENAME_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_TABLENAME_purge ();
CREATE FUNCTION sql_TABLENAME_purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN 

     -- Now delete one by one. 
   FOR record_backup IN SELECT * FROM TABLENAME where active = 0
      LOOP
         -- Record the id we want to delete. 
      delete_id = record_backup.TABLENAME_id;

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''purge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.   
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Get row count of row just deleted, should be 1. 
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.  
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_TABLENAME_purgeone (int4);
CREATE FUNCTION sql_TABLENAME_purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id < 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM TABLENAME 
      where active = 0 and TABLENAME_id = delete_id 
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = delete_id
      LOOP

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (record_backup.TABLENAME_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             BACKUPVALUES , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
drop function sql_TABLENAME_unpurge ();
CREATE FUNCTION sql_TABLENAME_unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD; 
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged. 
   FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup 
       where TABLENAME_backup.error_code = ''purge''
          and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
      LOOP

      purged_id := record1.TABLENAME_id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date. 

      FOR record2 IN select max(oid) from TABLENAME_backup 
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP 
          -- record we got the date and also record the highest date.
        oid_found := 1; 
        highest_oid := record2.max;
      END LOOP;
 
         -- If the oid_found is 0, return error. 
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them. 
      FOR record_backup IN select * from TABLENAME_backup 
          where oid = highest_oid
        LOOP 

      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (purged_id, record_backup.date_updated, 
             timestamp1, record_backup.active
             BACKUPVALUES , ''unpurge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;

      insert into TABLENAME (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             BACKUPVALUES );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- We got this far, it must be true, return how many were affected.  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_TABLENAME_unpurgeone (int4);
CREATE FUNCTION sql_TABLENAME_unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup
       where TABLENAME_backup.error_code = ''purge''
          and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
          and TABLENAME_id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Now find the highest oid.  
   FOR record2 IN select max(oid) from TABLENAME_backup
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it. 
    FOR record_backup IN select * from TABLENAME_backup 
          where oid  = highest_oid
        LOOP 
        -- Insert into backup that it was unpurged. 
      insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS ,error_code)
           values (purged_id, timestamp1, 
             record_backup.date_created, record_backup.active
             BACKUPVALUES , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insert into live table. 
      insert into TABLENAME (TABLENAME_id, date_updated, date_created,
          active BACKUPCOLUMNS)
           values (record_backup.TABLENAME_id, timestamp1,
             record_backup.date_updated, record_backup.active
             BACKUPVALUES );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

and lastly Custom.sql.

---          Custom Sample SQL for Perl/PostgreSQL version 0.1

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

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 to show you what this function cleans up. 
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up. 
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (1111);

After you save the perl script, execute "chmod 755 Create_Functions.pl" and then "./Create_Functions.pl". That should do it.

If you have installed PostgreSQL and Perl correctly, and you have setup the database and your account has permissions to that database, then everything should have worked fine.

Considerations to explore.

I would like to test TCL, Python, and other languages as well for stored procedures. If you are using MySQL, and I don't believe it has stored procedures, you may want to consider PostgreSQL if you like the style I mentioned. A nice GUI application to create tables and make changes to tables would be nice. Lastly, examples of how to connect to the database server to use these stored procedures (using Perl, Python, PHP, TCL, C, etc) would be nice.

Conclusion

The combination of PostgreSQL and Perl rocks. I can use Perl for three things, stored procedures, to setup my database, and to make Perl modules for Apache that connect to the PostgreSQL database. Similar stuff can be accomplished with other programming languages like Python, TCL, and others. I want to try Python at some point when it gets out of beta for PostgreSQL.

All database servers should use procedures exclusively for changing data. You could even argue that you should make custom stored procedures for selecting data as well. The reason why this is so important is because the web programmer (or other type of programmer) doesn't have to know anything about how to manipulate the data. They just submit variables to procedures. This lets the web programmer use any programming language he/she wants to without changing the behaviour of the database. The database and how you use it becomes abstract.

One stupid thing my perl script does is execute the custom sql code for each table. This s very bad. I will have to go back and fix it later. You may want to test my stuff out with these commands:

select sql_account_insert();
select sql_account_delete(1);
select sql_account_insert();
select sql_account_update(2,'mark','nielsen');
select sql_account_purge();
select sql_account_unpurge();
select * from account_backup;
select sql_account_delete(2);
select sql_account_insert();
select sql_account_update(1,'john','nielsen');
select sql_account_purge();
select * from account_backup;

References

My Previous PostgreSQL article.
If this article changes, it will be available at http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.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.