Re: trigger that needs a PK

On Wednesday 13 February 2008 12:35:27 am A. Kretschmer wrote:
> am  Tue, dem 12.02.2008, um 23:46:41 -0800 mailte johnf folgendes:
> > > http://www.postgresql.org/docs/current/static/functions-sequence.html
> > >
> > >
> > > HTH, Andreas
> >
> > But how do I do automaticly???
> >
> > You code implies that I just string two inserts together.  I was hoping
> > to use rules or some other way to do it automaticly.
>
> currval() returns the last inserted value within the current session, it
> is the usual way to insert into the parent table and use currval() to
> obtain the last inserted serial value for inserts into the child table.
>
>
> Andreas

After much reading I have the following code - please help me review:

-- Function: addrectolots()

-- DROP FUNCTION addrectolots();

CREATE OR REPLACE FUNCTION addrectolots()
  RETURNS "trigger" AS
$BODY$DECLARE
  next_aglot CURSOR FOR select currval('aglots_pkid_seq1') as fkey;
  --cur_aglot CURSOR FOR select aglots.pkid from public.aglots where
aglots.clot = new.clot;

  myaglot_id public.aglots.pkid%TYPE;
BEGIN


  --IF tg_op = 'INSERT' THEN
     insert into aglots (clot,fk_species,fk_variety,fk_agpoptrs) values
(new.clot,new.fk_species,new.fk_variety,new.pkid);
         OPEN next_aglot ;
     FETCH next_aglot INTO myaglot_id;
         new.fk_aglots := myaglot_id;
  --END IF;

  RETURN new;
END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION addrectolots() OWNER TO johnf;
GRANT EXECUTE ON FUNCTION addrectolots() TO public;
GRANT EXECUTE ON FUNCTION addrectolots() TO johnf;

--
John Fabiani