Thread: EXTPROC External Procedure

I cannot find any information about wether PostgreSQL has EXTPROC or not. Can
someone tell me if it dose or if there is a better way to do this.

I need to be able to run a program when some data is requested from the
database. Some query will do a SELECT on a known table and I need to have
some event trigger that makes a call to generate a small XML file and then
post it to a web server, get a reply back and then update a local table with
the information it received.

I have looked at how to do this in Oracle but I want to use PostgreSQL.
For Oracle I found this, http://www.orafaq.com/faqplsql.htm#OSCOMMAND. In
Oracle I need to make an EXTPROC call a bash script that will run wget and
then the bash script will make a new connection to the database to send it
the result data. The EXTPROC will not require any returned information. It
just needs to start the action.

Is there any way this can be done in PostgreSQL? Is there some other way to do
this?

On Wednesday February 9 2005 11:29, Roy Souther wrote:
> I cannot find any information about wether PostgreSQL has
> EXTPROC or not. Can someone tell me if it dose or if there is
> a better way to do this.

Not sure what version you're using, but sounds like a job for
perl (plperl) + a select rule (see CREATE RULE).

Ed

On Wed, Feb 09, 2005 at 11:29:21AM -0700, Roy Souther wrote:

> I need to be able to run a program when some data is requested from the
> database. Some query will do a SELECT on a known table and I need to have
> some event trigger that makes a call to generate a small XML file and then
> post it to a web server, get a reply back and then update a local table with
> the information it received.

You may be able to use a pl/perlu (untrusted perl) function to do this.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"No reniegues de lo que alguna vez creíste"

Hello,

code for genereting xml:

CREATE OR REPLACE FUNCTION ext.xml(varchar) RETURNS text AS $$
   use DBI; use XML::Generator::DBI; use XML::Handler::YAWriter;
   my $rv = spi_exec_query("SELECT current_setting('client_encoding'), " .
                           "current_database()", 1);
   my $encoding = lc($rv->{rows}[0]->{current_setting[5~});
   my $cdb = $rv->{rows}[0]->{current_database};
   my $dbh = DBI->connect ("DBI:Pg:dbname=$cdb", "", "", { RaiseError =>
1, PrintError => 0});
   my $ya = XML::Handler::YAWriter->new (Encoding=>$encoding,
        Pretty => {PrettyWhiteNewline => 1, PrettyWhiteIndent => 1,});
   my $gen = XML::Generator::DBI->new (Handler => $ya, dbh => $dbh,
                           ByColumnName => 1, ShowColumns => 1,);
   $gen->execute ($_[0]);
   $dbh->disconnect ();
   return join('',@{$ya->{Strings}});
$$ LANGUAGE plperlu;
SELECT ext.xml('SELECT * FROM ext.jmena LIMIT 10');

Sending mail, or uploud file via HTTP isn't problem. PL/Perlu works very
fine.

Regards
Pavel Stehule