plpgsql problem

From: Lehel Gyuro <lehel(at)bin(dot)hu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: plpgsql problem
Date: 2001-04-17 08:04:05
Message-ID: 130494311.20010417100405@bin.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

CREATE FUNCTION userHasAll (int4,int4) RETURNS boolean AS '
DECLARE
row RECORD;
kirakorow kirakok%ROWTYPE;
userID ALIAS FOR $1;
kirakoID ALIAS FOR $2;
megvan int4:=0;
kepdarabok INTEGER:=0;
query text;
BEGIN
SELECT * INTO kirakorow FROM kirakok WHERE kirako_id=kirakoID;
-- this works

IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid kirakoID'';
RETURN ''f'';
END IF;

kepdarabok:=kirakorow.kepdarabokx*kirakorow.kepdaraboky;
megvan:=0;

FOR row IN EXECUTE ''SELECT count(*) AS hits FROM talalatok WHERE userid='''''' || userID || '''''' AND jatek='''''' || kirakoID || '''''';'' LOOP
-- this works too but if you replace it with the following row :
-- FOR row IN SELECT count(*) AS hits FROM talalatok WHERE userid=userID AND jatek=kirakoID LOOP
-- this executes as if the following query was issued
-- FOR row IN SELECT count(*) AS hits FROM talalatok WHERE jatek=kirakoID LOOP
megvan:=row.hits;
END LOOP;
-- the same applies to inline queries too. if issued with execute
-- everything is fine, but if the query has more than one arguments
-- the compiler dismisses all, except the last one

IF megvan<>kepdarabok THEN
RETURN ''f'';
END IF;

RETURN ''t'';
END;
' LANGUAGE 'plpgsql';

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zeugswetter Andreas SB 2001-04-17 08:26:25 AW: timeout on lock feature
Previous Message Christopher Masto 2001-04-17 04:19:06 Re: Re: No printable 7.1 docs?