Re: A question about PL/pgSQL

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