SELECT FOR UPDATE in function
От
Oleg Bartunov
Тема
SELECT FOR UPDATE in function
Дата
Msg-id
Pine.GSO.3.96.SK.990725000710.18633F-100000@ra
Список
Дерево обсуждения
SELECT FOR UPDATE in function Oleg Bartunov <oleg@sai.msu.su>
RE: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function "Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function Tom Lane <tgl@sss.pgh.pa.us>
Is't possible to use SELECT FOR UPDATE in functions ? I have function for 'insert or update' which works ok, but as I have some problem with duplicated records I tried as suggested by Tom Lane to use SELECT FOR UPDATE instead of just select. Unfortunately it doesn't works: ERROR: query didn't return correct # of attributes for *internal* Here is a function: CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS ' Declare keyval Alias For $1; cnt int4; curtime datetime; Begin curtime := ''now''; -- Select count into cnt from hits where msg_id = keyval FOR UPDATE; Select count into cnt from hits where msg_id = keyval; if Not Found then cnt := 1; -- first_access inserted on default, last_access is NULL Insert Into hits (msg_id,count) values (keyval, cnt); else cnt := cnt + 1; Update hits set count = cnt,last_access = curtime where msg_id = keyval; End If; return cnt; End; ' LANGUAGE 'plpgsql'; Regards, Oleg PS. Just to test: create table hits ( msg_id int4 not null primary key, count int4 not null, first_access datetime default now(), last_access datetime ); select acc_hits(1); _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
В списке pgsql-hackers по дате отправления
От: Mike Mascari
Дата: