Thread: A question about PL/pgSQL

Greetings.

I am writting up a function in PL/pgSQL to automate a couple of multi-table
updates, and I have a question.

I need to check to see if the data is already in the database, and if it is,
perform and update, or if it's not, perform an insert.  The lookup is
against a primary key, so I only have to worry about 1 or 0 rows being
returned from the select statement.

I attempted something like

IF SELECT id FROM overview WHERE id = 100 ...

but I get an 'error near SELECT' returned from the database.

Any help would be apprieciated..  :)

Sincerely, Warren

On Fri, 27 Oct 2000, Warren Vanichuk wrote:

>
> Greetings.
>
> I am writting up a function in PL/pgSQL to automate a couple of multi-table
> updates, and I have a question.
>
> I need to check to see if the data is already in the database, and if it is,
> perform and update, or if it's not, perform an insert.  The lookup is
> against a primary key, so I only have to worry about 1 or 0 rows being
> returned from the select statement.
>
> I attempted something like
>
> IF SELECT id FROM overview WHERE id = 100 ...
>
> but I get an 'error near SELECT' returned from the database.
declare foo record;
begin
 select into foo * from overview...
 if not found
   insert...
 else ...
 end if;
end;

On Fri, Oct 27, 2000 at 02:32:28PM -0700, Warren Vanichuk wrote:
>
> Greetings.
>
> I am writting up a function in PL/pgSQL to automate a couple of multi-table
> updates, and I have a question.
>
> I need to check to see if the data is already in the database, and if it is,
> perform and update, or if it's not, perform an insert.  The lookup is
> against a primary key, so I only have to worry about 1 or 0 rows being
> returned from the select statement.
>
> I attempted something like
>
> IF SELECT id FROM overview WHERE id = 100 ...
>
> but I get an 'error near SELECT' returned from the database.
>
> Any help would be apprieciated..  :)
select id from from overview where id=100;
if not found then
...
end if;

Or you can make RULE like this (and you don't need proc):
CREATE RULE rule_tlink_insert AS ON insert TO tlink
 WHERE 0 NOT IN (SELECT count(*) FROM tlink WHERE fid=new.fid AND wid=new.wid)
    DO INSTEAD
       update tlink set cnt=cnt+1 WHERE fid=new.fid AND wid=new.wid;

If there is record in relation, then this rule updates counter, else
insert occurs as usually.
So,
"insert into tlink(cnt,fid,wid) values(1,v_fid,v_wid);"

inserts new record, or just adds 1 to counter.

--
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga",
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

Doesn't the following allow a race condition?

>declare foo record;
>begin
> select into foo * from overview...
> if not found
>   insert...
> else ...
> end if;
>end;

ISTM 2 or more clients could attempt to insert the same row, based on the select failing, if all were issued at the
sametime.  I've always assumed that, there being no row to lock in a _failed_ search, even saying SELECT ... FOR UPDATE
inthis case is no help. 


Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com