Re: SQL function

On Jul 28, 2005, at 12:13 PM, Prasad dev wrote:

> I want an SQL function a rough structure below-
>
> CREATE FUNCTION ins_both(int,int,int,int) RETURNS boolean AS '
> BEGIN
> INSERT INTO inv2 values($2,$3,$4);
> INSERT INTO inv1 values($1,$2,$3);
> COMMIT
> ' LANGUAGE SQL;
>
>
> but we cannot include Begin and commit in an SQL function
> so i came out with the following which is not right

The function is implicitly wrapped in a transaction. Either both
INSERTS will occur, or they'll both fail. You don't need to add START
TRANSACTION, END TRANSACTION, or COMMIT in the function body (nor can
you, which you've already discovered).

This should do what you want:

CREATE FUNCTION ins_both(int,int,int,int)
RETURNS boolean
LANGUAGE SQL AS $$
INSERT INTO inv2 values($2,$3,$4);
INSERT INTO inv1 values($1,$2,$3);
$$;

Michael Glaesemann
grzm myrealbox com