PL/Perl Getting Data From External File in PostgreSQL/PPAS

Till PostgreSQL 9.0, PostgreSQL doesn't have any feature which can be use to read the external file (a concept of External Table).

External Table is a feature of Database using which, any one can read the flat files as if it were in a table in the database.

So, I thought give an idea how user can implement this in Database using plperl.

Let's see how you can implement it.

I have a flat file, which has following data:

Filename: test.data
Location: /tmp/test.data

1,Check1
2,Check2
3,check3
4,check4
5a,check5

5a,check5

Since, I know this flat file is having two field (numeric and text), so,I can write a plperl function, which can read the file and can get the attribute(column values) of a line on the basis of passed delimiter, with this if there is any bad record, that will go to the bad file for verification if required.

Following is a sample plperl function:

CREATE OR REPLACE FUNCTION read_external(text,text) returns table(id numeric, col1  text)  
as  
$$  
## Written by Vibhor to Read external file
my $badfile = ">>"."$_[0]".".bad";
open (BADFILE,$badfile);
open (DATFILE, $_[0]);
while ($line=) {
  chomp($line);
  @line=split($_[1],$line);

if ($line[0] =~ /^[0-9]+$/ && $line[0] !="") 
{
    return_next({id => $line[0], col1 => $line[1]}); }
else {
  print BADFILE "$line\n"; 
  }
}
close(DATFILE);
close(BADFILE);
return undef;  
$$ language plperlu;

And I have made a view which can be use to SELECT the records in flat file as a table. Following is a result

postgres=# create or replace view external_table as select * from read_external('/tmp/test.data',',');
CREATE VIEW
postgres=# select * from external_table;
 id |  col1  
----+--------
  1 | Check1
  2 | Check2
  3 | check3
  4 | check4
(4 rows)

And I know, there is a bad record: 5a,check5, which I can find in bad file:

postgres=# \! cat /tmp/test.data.bad
5a,check5