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

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