Skip site navigation (1) Skip section navigation (2)

Re: trigger that needs a PK

From: johnf <jfabiani(at)yolo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: trigger that needs a PK
Date: 2008-02-13 15:11:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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:
> > >
> > >
> > >
> > > 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();

  RETURNS "trigger" AS
  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;
  --IF tg_op = 'INSERT' THEN
     insert into aglots (clot,fk_species,fk_variety,fk_agpoptrs) values 
         OPEN next_aglot ;
	 FETCH next_aglot INTO myaglot_id;
         new.fk_aglots := myaglot_id;
  --END IF;
  RETURN new;

ALTER FUNCTION addrectolots() OWNER TO johnf;
GRANT EXECUTE ON FUNCTION addrectolots() TO public;
GRANT EXECUTE ON FUNCTION addrectolots() TO johnf;

John Fabiani

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2008-02-13 15:25:02
Subject: Re: trigger that needs a PK
Previous:From: A. KretschmerDate: 2008-02-13 08:35:27
Subject: Re: trigger that needs a PK

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group