Re: A question about PL/pgSQL
От | Igor Roboul |
---|---|
Тема | Re: A question about PL/pgSQL |
Дата | |
Msg-id | 20001028093646.A30477@linux.rainbow обсуждение исходный текст |
Ответ на | A question about PL/pgSQL (Warren Vanichuk <pyber@street-light.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: