Use Postgresql functions to return operating information as rows

These functions access the underlying operating system, match the results with a table and return matching entries as rows.

Use the untrusted Perl module so that we get access to the underlying operating system.

createlang plperlu myCoolDb

createlang plpgsql myCoolDb

This example uses cursors, arrays, linux/unix operating system commands, and record sets.

It works with Postgesql 7.4, 8.0, 8.1, 8.2, 8.3, 8.4

However, the newer versions of postgesql now allow the perl script to access background tables better without DBD::PgSQL and supports SETOF with return_next support as well. This means that we wouldn't need to use cursors and have a separate function to return the data set.

I had problems with reading text lines from channels in Perl using the Greater and Less than signs. <FINDIMPORTFILE> returned Errors over the symbols. I switched to reading the entire result using the read command. I assumed that 10000 bytes were sufficient to pull in all data. The number can be larger than the available data.

This code looks for a file called ImportFile in all 3 character directories within the usr directory. It then makes sure that the 3 character directory is a current customer. If so, it returns the company code.

Example use:

select * from companies_waiting_import();

Code:

CREATE OR REPLACE FUNCTION import_file_list () RETURNS text AS '
my $n;
my @filelist;
my @itemlist;
my $newlist;
open (FINDIMPORTFILE,"find //usr//??? -name ImportFile -print|");
$n = read FINDIMPORTFILE, $data, 10000;
@filelist=split(''\n'',$data);
$newlist="false";
close (FINDIMPORTFILE);
foreach (@filelist) {
  @itemlist = split(''/'',$_);
  if (@itemlist[5] eq ''ImportFile''){
    if ($newlist eq ''false''){
      $newlist = "company=''".uc @itemlist[4]."''";
    } else {
      $newlist = "$newlist OR company=''".uc @itemlist[4]."''";
    }
  }
}
return $newlist;
' LANGUAGE plperlu;

CREATE OR REPLACE FUNCTION companies_waiting_import() RETURNS SETOF text AS '
DECLARE
  rec1 RECORD;
BEGIN
  FOR rec1 IN  EXECUTE ''SELECT company from companies where '' || import_file_list() || '' ORDER BY company'' LOOP
    return next rec1.company;
  END LOOP;
  return undef;
END;
' LANGUAGE plpgsql;