triggers & functions

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(at)aries(dot)tucson(dot)saic(dot)com