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=<datfile>) {
  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

2 Comments

  1. Can you give any help with installing plperl on enterpriseDB’s version of postgresql. The only way I seem to be able to do this is using the the versions in which come the Mandriver , PGlinuxOS & Lenovo distributions
    which are all old versions.

    1. Sure. Let me know issues which you are facing. For creating plperl in Advanced Server 9.0, you should have ActivePerl 5.12 installed. You can execute following command after connecting to Database:

      CREATE LANGUAGE plperl;
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s