triggers & functions - Mailing list pgsql-general

Hi,

I'm porting a database from Oracle, and I'm having difficulty
working out the syntax & logic for porting the triggers.

Here's an example of what I have in Oracle:

create table Images (
  id             varchar(100)   PRIMARY KEY,
  title          varchar(25)    NOT NULL,
  filepath       varchar(256)   NOT NULL UNIQUE,
  status_code    varchar(5)     NOT NULL
) ;

create table Istatus (
  status_code    varchar(5)     PRIMARY KEY,
  status_desc    varchar(100)   NOT NULL
);

When I delete a record in Istatus, I want to delete any records in
Images that have the given status code.  Okay, this is a rather
crude example, but I think if I can do this, I can do the rest.

In Oracle, I write the trigger something like this:

CREATE TRIGGER istatus_delete_trigger
  AFTER DELETE ON Istatus
  FOR EACH ROW
  BEGIN
    delete from Images i
    where i.status_code = :old.status_code;
  END;

Based on the documents and regression tests in the distribution, it
looks like I need to move the meat of the trigger into a function
for postgres.  In postgres I'll call the procedure from the trigger.

Well, after going through the docs & looking at examples, I haven't
figured it out.  My inclination is to write:

CREATE FUNCTION remove_status_func()
  RETURNS int4 AS '
  delete from Images
  where Images.status_code = old.status_code ;
  select 1 as val;
  ' LANGUAGE 'sql' ;

I don't want to return anything, but that doesn't seem to be an
option. Is opaque equivalent to no return value?  I couldn't find it
in the docs. Postgres gave me a message that opaque types weren't
allowed if the language is sql.  Why?

So I have a dummy return value, but now Postgres doesn't seem to
like the reference to 'old'.  I see examples of functions which use
'old' in the plpgsql.sql regression set, but those specify a
different language (even though that language looks like sql).  I
didn't find the definition of that language after poking around.

Can anyone set me straight here?  An example of a trigger which
calls a sql procedure would be much appreciated!  I'd like the
function to be able to access the rows which are being removed.

Thanks,

Sarah Officer
officers@aries.tucson.saic.com