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: 200802130711.51948.jfabiani@yolo.com (view raw or flat)
Thread:
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:
> > > 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

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-2014 The PostgreSQL Global Development Group