plpgsql: Bush becomes a null, can not execute null query

From: Mauri Sahlberg <Mauri(dot)Sahlberg(at)claymountain(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: plpgsql: Bush becomes a null, can not execute null query
Date: 2003-08-28 07:34:28
Message-ID: 1062056068.19315.24.camel@taekwondo.intra.pretax.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

This time I think this is a bug, but where? In my code or in plpgsql? Or
in my understanding of dynamic queries?

If the following is executed in context, where the dynamic query returns
data with every iteration everythin goes fine. If the the dynamic query
returns nothing - bush becomes null and everything fails. I do not
undestand why bush becomes null when the query returns nothing.

CREATE OR REPLACE FUNCTION
Paivat(varchar(9), date, date,varchar, integer)
RETURNS setof PS_TYOSUORITUKSET AS '
DECLARE
aYritys ALIAS FOR $1;
aPvm ALIAS FOR $2;
alPvm ALIAS FOR $3;
aKtunnus ALIAS FOR $4;
aTunniste ALIAS FOR $5;

rivi PS_TYOSUORITUKSET%ROWTYPE;
latest INTEGER;
nyt DATE;
bush text;

BEGIN
nyt:=aPvm;
latest:=0;
WHILE nyt <= alPvm LOOP
bush:=''select * from PaivanLeimat('' ||
quote_literal(aYritys) ||
''::varchar,'' ||
quote_literal(nyt) ||
''::date,''||
quote_literal(aKtunnus) ||
''::varchar,'' ||
latest
||'')'';
RAISE NOTICE ''Query: %'', bush;
FOR rivi IN EXECUTE bush LOOP
RAISE NOTICE ''Ugh:%'',rivi.ytunnus;
RETURN NEXT rivi;
END LOOP;
latest:=rivi.suoritus;
nyt:=nyt + interval ''1 day'';
RAISE NOTICE ''%'', nyt;
END LOOP;

RETURN;
END;
' LANGUAGE 'plpgsql';

select * from Paivat('0107262-0'::varchar, '2003-07-01'::date,
'2003-08-20'::date, 'colly_stig',0);

NOTICE: Query: select * from
PaivanLeimat('0107262-0'::varchar,'2003-07-01'::date,'colly_stig'::varchar,0)
NOTICE: Ugh:0107262-0
NOTICE: Ugh:0107262-0
NOTICE: Ugh:0107262-0
NOTICE: 2003-07-02
NOTICE: Query: select * from
PaivanLeimat('0107262-0'::varchar,'2003-07-02'::date,'colly_stig'::varchar,26516)
NOTICE: 2003-07-03
NOTICE: Query: <NULL>
WARNING: Error occurred while executing PL/pgSQL function paivat
WARNING: line 27 at for over execute statement

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bruno LEVEQUE 2003-08-28 12:16:24 Re: How do i get the location or -D paramter to accept a windows
Previous Message Tom Lane 2003-08-28 03:30:27 Re: Disk Utilization Increases And Time for Vacuum Increases.