PostgreSQL Discussion - Altering built-in functions cast

Author

Altering built-in functions cast

Matt A.

2005-08-25, 8:24 pm

Anyone know how I could alter the cast of the nullif()
function directly to return INT?

We use NULLIF() for adding [1|0|null] according to the
evalution of nullif('x','') into integer columns.
Where x is an integer and sometimes a empty string,
which if it's an empty string (x='') then we add NULL
cause NULLIF says if '' == '' then return NULL

Thank you,
matthew

____________________
______________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Richard Huxton

2005-08-26, 7:23 am

Matt A. wrote:
> Anyone know how I could alter the cast of the nullif()
> function directly to return INT?
>
> We use NULLIF() for adding [1|0|null] according to the
> evalution of nullif('x','') into integer columns.
> Where x is an integer and sometimes a empty string,
> which if it's an empty string (x='') then we add NULL
> cause NULLIF says if '' == '' then return NULL

Just add a wrapper function:

CREATE FUNCTION nullif_always_int(te
xt) RETURNS integer AS '
SELECT nullif($1,'''')::int
;
' LANGUAGE SQL;

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Matt A.

2005-08-26, 7:23 am

Thank you so much for the reply. I inserted the
function as you said in pgsql. I tried testing it and
got...

PERFECT RESULTS!

THHHHHHHHHHHHHAAAAAA
AAAAANKKKKKKKKKKK YOUUUUUUUUUUU!

*kisses feet* j/k But honestly, Thank you very much.
You saved me from suicide.

--- Richard Huxton <dev@archonet.com> wrote:

> Matt A. wrote:
> nullif()
> the
> string,
> NULL
>
> Just add a wrapper function:
>
> CREATE FUNCTION nullif_always_int(te
xt) RETURNS
> integer AS '
> SELECT nullif($1,'''')::int
;
> ' LANGUAGE SQL;
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

____________________
____________________
____________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links