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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
