Thread

1. plpgsql problem

   Lehel Gyuro <lehel@bin.hu> — 2001-04-17T08:04:05Z

   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';
   
   
   
   

2. Re: plpgsql problem

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-18T04:30:59Z

   Lehel Gyuro <lehel@bin.hu> writes:
   > -- 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
   
   This is more than slightly hard to believe.  There are thousands of
   people using plpgsql, and you're the first to notice that it loses all
   but the last WHERE qualifier?  Nyet.  There's more to it than that,
   surely.
   
   Perhaps you could provide a *complete* example?  The text of the
   function is far from enough to let someone else try to reproduce
   your problem.  We need a script that creates all the referenced
   tables, and puts sample data in them, and creates and invokes the
   function with appropriate test data.  And perhaps you could tell us
   what output you got and what you expected to get, and why that led
   you to conclude that there is a failure of the above-claimed form.
   
   			regards, tom lane
   
   

3. Re: plpgsql problem

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-04-18T05:54:42Z

   My guess is that since userid and userID differ only in
   case, it's probably not actually using the aliased version
   and instead is using only the column one.  
   
   The execute is different since you're effectively putting
   the *value* of userID into the query as opposed to the word.
   I'd suggest renaiming the alias and seeing if that works.
   
   On Tue, 17 Apr 2001, Lehel Gyuro wrote:
   
   > 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';
   > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   >