pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning NULL results?
3+ messages / 2 participants
[nested] [flat]
* Returning NULL results?
@ 2005-10-11 14:27 Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Splivalo @ 2005-10-11 14:27 UTC (permalink / raw)
To: pgsql-sql
_SQL := 'SELECT TmessageId FROM tmpBids WHERE TphoneNumber = '
|| quote_literal(phoneNumber)
|| ' AND Tbid = ' || aBid;
FOR rec IN EXECUTE _SQL LOOP bidCount := rec._qv; END LOOP;
This works ok as long as the SELECT query returns rows. Of course, if it
returns multiple rows bidCount variable will hold just the last row
value, but the design of application is like that so the query from the
start returns only one row, or returns no rows.
Of course, if it returns no rows, I'm presented with an error, saying:
ERROR: record "rec" has no field "_qv"
This is logical. My question would be is there a way around this
withouth first doing SELECT COUNT(*) FROM tmbBids WHERE ..., and then if
COUNT(*) is zero THEN bidCount := NULL ELSE if COUNT(*) is not zero then
do SELECT from the begining?
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning NULL results?
@ 2005-10-11 14:39 Richard Huxton <dev@archonet.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2005-10-11 14:39 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo wrote:
>
> Of course, if it returns no rows, I'm presented with an error, saying:
>
> ERROR: record "rec" has no field "_qv"
>
> This is logical. My question would be is there a way around this
> withouth first doing SELECT COUNT(*) FROM tmbBids WHERE ..., and then if
> COUNT(*) is zero THEN bidCount := NULL ELSE if COUNT(*) is not zero then
> do SELECT from the begining?
Could you check the FOUND variable? As in IF NOT FOUND THEN RETURN NULL
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning NULL results?
@ 2005-10-12 07:36 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mario Splivalo @ 2005-10-12 07:36 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Tue, 2005-10-11 at 15:39 +0100, Richard Huxton wrote:
> Mario Splivalo wrote:
> >
> > Of course, if it returns no rows, I'm presented with an error, saying:
> >
> > ERROR: record "rec" has no field "_qv"
> >
> > This is logical. My question would be is there a way around this
> > withouth first doing SELECT COUNT(*) FROM tmbBids WHERE ..., and then if
> > COUNT(*) is zero THEN bidCount := NULL ELSE if COUNT(*) is not zero then
> > do SELECT from the begining?
>
> Could you check the FOUND variable? As in IF NOT FOUND THEN RETURN NULL
>
That actually works, but it's a bit of fuss to use. I need to have it
inside the FOR loop, then exit the loop if FOUND is NOT TRUE, wich makes
the code even harder to read/mantain. Do you think that anytime soon
would we be able to do SELECTs from the temporary tables withouth
EXECing those selects? And, some qualifier for the variables (@ in
T-SQL, $ in PHP, or something similair) would make the code even more
readable. Just my tought.
Btw, on postgres 7.4 you can do SELECT something FROM ...., and if query
returns no rows, the FOR loop is skipped. So, it's safe to do something
like:
someVar:=NULL;
_SQL:='SELECT someVar FROM someTable WHERE someCondition...'
FOR rec IN EXECUTE _SQL LOOP someVar:=rec.someVar; END LOOP;
Now, if select returns no rows, the LOOP/END LOOP is skippend, and
someVar is se to be null before FOR.
Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-12 07:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-11 14:27 Returning NULL results? Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-11 14:39 ` Richard Huxton <dev@archonet.com>
2005-10-12 07:36 ` Mario Splivalo <mario.splivalo@mobart.hr>
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