pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with FOR SELECT in plpgsql function
2+ messages / 2 participants
[nested] [flat]
* Problem with FOR SELECT in plpgsql function
@ 2004-02-20 10:07 Plant Thomas <Thomas.Plant@lvh.it>
2004-02-20 11:09 ` Re: Problem with FOR SELECT in plpgsql function Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Plant Thomas @ 2004-02-20 10:07 UTC (permalink / raw)
To: pgsql-sql
I have a problem with the following function:
CREATE OR REPLACE FUNCTION "weiterbildung"."f_termine_uhrzeit" (integer)
RETURNS text AS'
DECLARE
id ALIAS for $1;
dat RECORD;
uhrzeiten text;
BEGIN
uhrzeiten = '''';
FOR dat IN SELECT vormittag_a, vormittag_e, nachmittag_a, nachmittag_e
FROM weiterbildung.t_termine where id_kurs = id order by datum
LOOP
uhrzeiten :=
uhrzeiten||to_char(dat.vormittag_a,''HH24:MI'')||''(''||to_char(dat.vormitta
g_a,''HH24:MI'')||''-''||to_char(dat.vormittag_e,''HH24:MI'')||''
''||to_char(dat.nachmittag_a,''HH24:MI'')||''-''||to_char(dat.nachmittag_e,'
'HH24:MI'')||'')'';
END LOOP;
RETURN uhrzeiten;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
This function sometimes returns null even if there is a record in the
database for the corrisponding id.
This happens only when there is only one record for the corrisponding id
value in the database, if there
are more than one record it works with no problem.
Is there a bug in the FOR SELECT statement?
Thanks for advice
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem with FOR SELECT in plpgsql function
2004-02-20 10:07 Problem with FOR SELECT in plpgsql function Plant Thomas <Thomas.Plant@lvh.it>
@ 2004-02-20 11:09 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2004-02-20 11:09 UTC (permalink / raw)
To: Plant Thomas <Thomas.Plant@lvh.it>; pgsql-sql
On Friday 20 February 2004 10:07, Plant Thomas wrote:
> I have a problem with the following function:
[snip]
>
> This function sometimes returns null even if there is a record in the
> database for the corrisponding id.
> This happens only when there is only one record for the corrisponding id
> value in the database, if there
> are more than one record it works with no problem.
If it actually returns NULL and not the empty string, then the loop must be
processed, since you set "uhrzeiten" to the empty string before the loop.
Two things to check:
1. None of your variables match a column in your select (and you seem OK
here).
2. None of the fields in your expression are null (e.g. dat.vormittag_a)
I think it's #2 - concat NULL to any string and you get NULL back.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-20 11:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 10:07 Problem with FOR SELECT in plpgsql function Plant Thomas <Thomas.Plant@lvh.it>
2004-02-20 11:09 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox