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

Re: how do the pro's do this? (still a newbie)

From: Tod McQuillin <devin(at)spamcop(dot)net>
To: Gunnar Lindholm <gunnar(at)gunix(dot)mine(dot)nu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: how do the pro's do this? (still a newbie)
Date: 2001-10-30 14:01:46
Message-ID: 20011030224829.K93584-100000@glass.pun-pun.prv (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 30 Oct 2001, Gunnar Lindholm wrote:

>  table A :
> 	id 	integer primary key default nextval('something'),
> 	dohA	integer
>
> table B:
> 	rid	integer references A,
> 	dohB	integer
>
> and I wish to create a function "foo( dohA, dohB)" that inserts the values
> dohA and dohB into the proper tables A and B and the reference in table B
> should of course be connected to the PK in table A.

Something like this should work.  I did not test it at all though.

CREATE FUNCTION foo(integer, integer) RETURNS integer AS '
	DECLARE
		a_id		A.id%TYPE;
	BEGIN
		a_id := nextval(''something'');
		INSERT INTO A VALUES (a_id, $1);
		INSERT INTO B VALUES (a_id, $2);
		RETURN a_id;
	END;
' LANGUAGE 'plpgsql';

I am not sure about starting new transactions inside plpgsql functions.
Since postgresql doesn't support nested transactions yet I think you may
have to begin and end the transaction outside of the function, like so:

BEGIN TRANSACTION;
SELECT foo(1, 2);
COMMIT;

I hope someone will correct me if I am wrong on this point.
-- 
Tod McQuillin




In response to

Responses

pgsql-general by date

Next:From: Simeo ReigDate: 2001-10-30 14:36:42
Subject: Re: how do the pro's do this? (still a newbie)
Previous:From: Tille, AndreasDate: 2001-10-30 13:59:25
Subject: Re: [HACKERS] Serious performance problem

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