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 по дате отправления:

Предыдущее
От: "Marc G. Fournier"
Дата:
Сообщение: Re: postgresql and replication
Следующее
От: Daniel Michał
Дата:
Сообщение: postgresql and lock an row