plpgsql: Bush becomes a null, can not execute null query
От | Mauri Sahlberg |
---|---|
Тема | plpgsql: Bush becomes a null, can not execute null query |
Дата | |
Msg-id | 1062056068.19315.24.camel@taekwondo.intra.pretax.net обсуждение исходный текст |
Ответы |
Re: plpgsql: Bush becomes a null, can not execute null query
Re: plpgsql: Bush becomes a null, can not execute null query |
Список | 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
В списке pgsql-admin по дате отправления: