DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding;
	b=L80rx7gksNsp7I7jS74wR2WevJ7IeecHQBxudw6RiCcltRHFh5px+rNWJEPNH6nEABesHGlpNLnY9Igpkv/K1cDs/YWcP+9KJfdtpCVnazquHen20+J+O/Xw8nQUOaS3QkLSCXM9Hy7ncArpL0fLQtaOgsWCuzLIXskuX+23bIY=
Message-ID: <6c21003b0501301141234c256b@mail.gmail.com>
Date: Sun, 30 Jan 2005 13:41:28 -0600
From: Don Drake <dondrake@gmail.com>
Reply-To: Don Drake <dondrake@gmail.com>
To: pgsql-sql@postgresql.org
Subject: plpgsql functions and NULLs
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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