plpgsql functions and NULLs

From: Don Drake <dondrake(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql functions and NULLs
Date: 2005-01-30 19:41:28
Message-ID: 6c21003b0501301141234c256b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

OK, I have a function that finds records that changed in a set of
tables and attempts to insert them into a data warehouse.

There's a large outer loop of candidate rows and I inspect them to see
if the values really changed before inserting.

My problem is that when I look to see if the row exists in the
warehouse already, based on some IDs, it fails when an ID is NULL.
The ID is nullable, so that's not a problem.

But I'm forced to write an IF statement looking for the potential NULL
and write 2 queries:

IF omcr_id is null
select * from ....
WHERE omcr_id is NULL
AND ...
ELSE
select * from ....
WHERE omcr_id=candidate.omcr_id
AND ....
END IF;

IF FOUND
...

Is there a way to do the lookup in one statement?? This could get ugly
quick. I'm using v7.4.

Thanks.

-Don

--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
312-560-1574

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ing. Jhon Carrillo 2005-01-31 18:59:12 error in function!!
Previous Message Mischa 2005-01-29 05:58:54 Re: same question little different test MSSQL vrs Postgres