Thread: 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@aries.tucson.saic.com

Sarah Officer wrote:

> 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.

How about examples of a trigger that calls a *PL/pgSQL* procedure that
has access to the rows being removed?

    http://www.deja.com/getdoc.xp?AN=570616874

Oh, and I don't understand the opaque business yet, either.  But it
seems I must return an opaque type for any function called directly by
a trigger, and that OLD and NEW are only available to that function...

Cheers,
Ed Loehr

Oh, and one other thing...

The example has a typo.  In the function, 'temp' and 'cust' should be the
same variable (doesn't matter what it's called).

Cheers,
Ed Loehr

Ed Loehr wrote:

> Sarah Officer wrote:
>
> > 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.
>
> How about examples of a trigger that calls a *PL/pgSQL* procedure that
> has access to the rows being removed?
>
>     http://www.deja.com/getdoc.xp?AN=570616874
>
> Oh, and I don't understand the opaque business yet, either.  But it
> seems I must return an opaque type for any function called directly by
> a trigger, and that OLD and NEW are only available to that function...
>
> Cheers,
> Ed Loehr

Did this help?  If not, what was missing? [I'm giving this advice
repeatedly, but not sure it's helping...]

Cheers,
Ed Loehr

Ed Loehr wrote:

> Oh, and one other thing...
>
> The example has a typo.  In the function, 'temp' and 'cust' should be the
> same variable (doesn't matter what it's called).
>
> Cheers,
> Ed Loehr
>
> Ed Loehr wrote:
>
> > Sarah Officer wrote:
> >
> > > 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.
> >
> > How about examples of a trigger that calls a *PL/pgSQL* procedure that
> > has access to the rows being removed?
> >
> >     http://www.deja.com/getdoc.xp?AN=570616874
> >
> > Oh, and I don't understand the opaque business yet, either.  But it
> > seems I must return an opaque type for any function called directly by
> > a trigger, and that OLD and NEW are only available to that function...
> >
> > Cheers,
> > Ed Loehr
>
> ************