Re: how do the pro's do this? (still a newbie) - Mailing list 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