
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



Sun, 25 Jul 1999 00:14:00 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
SELECT FOR UPDATE in function
>
> 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*
>

AFAIC,"SELECT FOR UPDATE" always causes above errors in
PL/pgSQL functions.
Could we use PL/pgSQL for update procedures in MVCC ?

ORDER/GROUP BY items that are not in the targetlist also cause
same errors in PL/pgSQL functions.
In both cases,target entries are added which are not wanted in the
final projected tuple(SELECT FOR UPDATE adds "ctid" entry).

In such cases,the # of target entries is different from the # of
final attributes estimated in pl_gram.y and above elog() in
pl_exec.c is called.

Should current check be loosen ?
Or another check is necessary ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



Wed, 28 Jul 1999 11:58:42 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> ERROR:  query didn't return correct # of attributes for *internal*

> AFAIC,"SELECT FOR UPDATE" always causes above errors in
> PL/pgSQL functions.

> ORDER/GROUP BY items that are not in the targetlist also cause
> same errors in PL/pgSQL functions.
> In both cases,target entries are added which are not wanted in the
> final projected tuple(SELECT FOR UPDATE adds "ctid" entry).

It sounds like the code that deals with the resulting tuple is not
smart enough to ignore resjunk attributes (or to call ExecProject
if it needs an actual junk-free tuple).  That's probably an easily
fixed bug, but I'm not familiar with the PL code...

			regards, tom lane

Wed, 28 Jul 1999 10:19:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function 