Thread: SQL function

Hi all !

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

Create function insboth(int,int,int,int) RETURNS void AS $$
START TRANSACTION;
INSERT INTO inv2 values($2,$3,$4);
INSERT INTO inv1 values($1,$2,$3);
END TRANSACTION;
$$LANGUAGE SQL;

In short what i look for, i commit only after inserting these 2 tuples and
later it fires my trigger etc. If there is any other possible way please let
me know.

Thanks

Cheers
Prasad.

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

On Jul 30, 2005, at 5:10 PM, Prasad dev wrote:

> Sorry for mailing ya instead of the forum, earlier i had asked you
> about SQL function, this what you said should work and it worked
> indeed.

Please don't top post, and please send replies to the mailing list.
I'm cc'ing -novice. You have a much better chance at getting a
helpful response if you always send to the mailing list for questions.

> CREATE  FUNCTION ins_both(int,int,int,int)
> RETURNS void
> LANGUAGE SQL AS $$
> INSERT INTO inv2 values($2,$3,$4);
> INSERT INTO inv1 values($1,$2,$3);
> $$;
>
> My problem is i have trigger which fires after insertion in Inv2,
> this trigger checks for matching values in Inv1, if a matching
> record is present in Inv1 then it inserts the tuple in Inv2. Is
> there any way by which i can insert both tuples in tables and then
> the trigger is fired for instance if i had a 'commit' as the last
> line of my  function then this would have fired the trigger and so on.
>
> Simple table structure is as follows:
> create table inv2 (b int, c int, d int, primary key (b,c));
> create table inv1(a int, b int, c int, primary key (a));

I don't know much about triggers, as I haven't often needed to use
them. How do you know the trigger is not firing? People who
understand triggers and other such things may want to see a
simplified test case (including trigger code) that shows the behavior
you're seeing.

Good luck!

Michael Glaesemann
grzm myrealbox com