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

Re: SQL function

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: "Prasad dev" <esteem3300(at)hotmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL function
Date: 2005-07-28 03:23:47
Message-ID: 498368E8-5ACE-4A9D-A281-ED0EE9DC902C@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2005-07-28 05:22:32
Subject: Re: Index help
Previous:From: Prasad devDate: 2005-07-28 03:13:17
Subject: SQL function

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