From: | Terry Lee Tucker <terry(at)esc1(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: quote_literal Simple question |
Date: | 2005-02-18 14:26:18 |
Message-ID: | 200502180926.18131.terry@esc1.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
You should be able to use a CASE WHEN statement inside this expression and
test for a null itemIdValue. If it is null the you can return ''NULL'' else,
return itemIdValue.
HTH
On Friday 18 February 2005 09:07 am, David Gagnon saith:
> Hi all,
>
> I did a stored procedure and ran into this small problem.
>
> Here itemIdValue may be null and I would have expected quote_literal to
> returns null as a string or the value quoted. For now I think it returns a
> real null. This causes my function to crash.
>
> insertStatement:= ''INSERT INTO IL ( ILNUM, ILTYPE, ILICNUM, ILRRNUM,
> ILQTE, ILPRIX, ts ) VALUES ( '' || idValue ||'', ''|| typeValue ||'','';
> insertStatement:= insertStatement ||
> quote_literal(itemIdValue) || '','';
> insertStatement:= insertStatement ||
>
>
> I haven't found an existing function to handle this case and I hate
> reinventing the Wheel... Is there a function that already handles that?
>
>
> For now I worked around this by using the COALESCE function.
>
> COALESCE(quote_literal(itemIdValue),'' null'') || '','';
> insertStatement:= insertStatement ||
>
> Thanks for your help
>
>
> /David
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Find what you need with new enhanced search.
> http://info.mail.yahoo.com/mail_250
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-02-18 14:29:05 | Re: Alternate DB location continued. |
Previous Message | David Gagnon | 2005-02-18 14:07:12 | quote_literal Simple question |