Обсуждение: plpgsql: Bush becomes a null, can not execute null query

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

Mauri Sahlberg <Mauri.Sahlberg@claymountain.com> writes:
> 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.

If "latest" is null then the result of the series of concatenations is
also null.  Perhaps what you really want is to assign to "latest" inside
the inner FOR-loop.

            regards, tom lane

Concatenating a null value makes the string null.  try:
SELECT 'foo'||NULL||'bar';

Just add a condition to check rivi.suoritus for null before making an
assignment.



Mauri Sahlberg wrote:

>returns nothing - bush becomes null and everything fails. I do not
>undestand why bush becomes null when the query returns nothing.
>
>

>     bush:=''select * from PaivanLeimat('' ||
>    quote_literal(aYritys) ||
>    ''::varchar,'' ||
>        quote_literal(nyt) ||
>        ''::date,''||
>        quote_literal(aKtunnus) ||
>        ''::varchar,'' ||
>        latest
>        ||'')'';
>
>
<snip>

>     latest:=rivi.suoritus;
>
>

Thank you very much. Also thanks for Josh.

I have to say that I'm really impressed with the speed of this list and
dedication of the people who develop postgres.

Twice I have asked a question on this list and twice I've got absolutely
correct answer in less than one day - even when both of my questions have
been kind of RTFM questions, had I just thought them out!

The only two places I've got answers so fast have been Datavision and
Inetd-software.

> Mauri Sahlberg <Mauri.Sahlberg@claymountain.com> writes:
>> 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.
>
> If "latest" is null then the result of the series of concatenations is
> also null.  Perhaps what you really want is to assign to "latest" inside
> the inner FOR-loop.
>
>             regards, tom lane
>
>

On Thu, 28 Aug 2003, Josh Goldberg wrote:

> Concatenating a null value makes the string null.  try:
> SELECT 'foo'||NULL||'bar';
>
> Just add a condition to check rivi.suoritus for null before making an
> assignment.

or better use coalesce

>
>
>
> Mauri Sahlberg wrote:
>
> >returns nothing - bush becomes null and everything fails. I do not
> >undestand why bush becomes null when the query returns nothing.
> >
> >
>
> >     bush:=''select * from PaivanLeimat('' ||
> >    quote_literal(aYritys) ||
> >    ''::varchar,'' ||
> >        quote_literal(nyt) ||
> >        ''::date,''||
> >        quote_literal(aKtunnus) ||
> >        ''::varchar,'' ||
> >        latest
> >        ||'')'';
> >
> >
> <snip>
>
> >     latest:=rivi.suoritus;
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

    Regards,
        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83