A question about PL/pgSQL

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 same time. I've always assumed that, there being no row to lock in a _failed_ search, even saying SELECT ... FOR UPDATE in this case is no help.

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