Re: How to pickup null values in SQL Language?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tahira Aslam
Sent: Thursday, May 30, 2002 7:42 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] How to pickup null values in SQL Language?

I have written this function in SQL Language in postgresql 7.2.

CREATE FUNCTION MyFunction() RETURNS SETOF Text AS '

Select
MyView."Document_ID"||','||MyView."First_Name"||','||MyView."Last_Name"||','
||MyView."Sent_Date" "From MyView" Where "Document_ID"=$1;

' LANGUAGE 'SQL';

It worked fine...returning me multiple records of concatenated
string,until there came a record where "Sent_Date" field was Null.It
returned nothing then.

I want to put a check to set null values equal to zero(namely to set
"sent_Date"=0 If it is Null) , but looks like SQL does not support "IF THEN
ELSE".plz let me know how can i put that check in this function.?

SQL supports CASE WHEN THEN ELSE, which works just like IF THEN ELSE.

However, you can do this without that. Try

CREATE FUNCTION MyFunction() RETURNS SETOF Text AS '
SELECT ... WHERE Document_ID=$1 OR ($1 IS NULL AND Document_ID IS NULL)'

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant