Re: Trigger to call an external program

Thanks for this Peter.  I got it working by putting in the changes
suggested by Stephan and adding errno.h

However, I can't get any triggers to work.  I'm new to postgresql, and
to triggers, so it's got to be something I'm doing wrong.

The test.sql script only half works.  The behaviour is the same on the
7.2.1 system, and on the 7.3.4 systems that I've hacked, so I don't
*think* that I've introduced a problem.

Using a pgplsh function works.  The test script first creates a function
shtest then runs it.  This works.  The second part of the script,
creating the function shtrigger, works.  The create of the trigger
testtrigger works, ie, I can see the created trigger in the table
pg_trigger.  But the 3 inserts insert the data correctly, but there is
no sign that the trigger is being fired off. I've tried various
functions to be fired as triggers, the simplest was
CREATE FUNCTION alantrigger() RETURNS opaque AS
'#!/bin/bash
echo "Hello world"
exit 0
' LANGUAGE 'plsh';
but none of them seem to have any effect.

The output of test.sql (on the 7.3.4 system) is as follows:
bash-2.05a$ psql -f test.sql
DROP FUNCTION
CREATE FUNCTION
      shtest
-------------------
 One: foo Two: bar
(1 row)

psql:test.sql:12: ERROR:  shtest: this is an error
DROP FUNCTION
CREATE FUNCTION
DROP TABLE
CREATE TABLE
psql:test.sql:29: NOTICE:  CreateTrigger: changing return type of
function shtrigger() from OPAQUE to TRIGGER
CREATE TRIGGER
INSERT 56863 1
INSERT 56864 1
INSERT 56865 1
cat: /home/e-smith/files/users/infonet/voodoo-pgplsh-test: No such file
or directory
rm: cannot remove
`/home/e-smith/files/users/infonet/voodoo-pgplsh-test': No such file or
directory

I made 2 minor changes to the script.  I put a CASCADE into the drop
function so it would drop the trigger as well,, and removed the drop
trigger statement.

Any thoughts as to how I can debug this further are greatly
appreciated.  I'm not sure what to try next.

Thanks

Alan Graham

On Sat, 2003-12-06 at 02:51, Peter Eisentraut wrote:
> On Thursday 04 December 2003 16:36, Stephan Szabo wrote:
> > > I thought I was onto a winner with pgplsh, but I can't get it to
> > > compile with 7.3.4, although it compiled beautifully with 7.2.1.
> >
> > It takes a little bit of work to get pgplsh to compile for my devel
> > system (notably that I needed to make sure the server includes were
> > in the -I path and had to make a few more changes noted below).
> >
> > I removed the if (DebugLvl > 1) lines and needed to change
> > elog(DEBUG, lines to one of the new DEBUG levels (I chose DEBUG2).
> > After that it seemed to compile.
>
> I'll try to make new "releases" over the weekend that compile properly
> for the various PostgreSQL versions that are around.