Thread: Extending COALESCE()

Extending COALESCE()

From

David Frankson

Date:

23 November 2010, 01:41:13

Is it possible to extend the COALESCE() function? I would like to support for coalescing an int into a Boolean, but I get syntax errors if I don’t wrap coalesce in quotes.

CREATE OR REPLACE FUNCTION coalesce(boolean,int) RETURNS boolean AS $$

SELECT CASE WHEN $1 IS NOT NULL THEN $1 WHEN $2 = 1 THEN true ELSE false END

$$ LANGUAGE sql IMMUTABLE;

Thanks,

Dave

Re: Extending COALESCE()

From

Pavel Stehule

Date:

23 November 2010, 04:07:48

Hello

I am afraid, so this isn't possible. You have to use a explicit cast
"int to bool"

select coalesce(true, 0::bool)

regards

Pavel Stehule

2010/11/23 David Frankson <David.Frankson@infinitecampus.com>:
> Is it possible to extend the COALESCE() function?  I would like to support
> for coalescing an int into a Boolean, but I get syntax errors if I don’t
> wrap coalesce in quotes.
>
>
>
> CREATE OR REPLACE FUNCTION coalesce(boolean,int) RETURNS boolean AS $$
>
>
>
>                 SELECT CASE WHEN $1 IS NOT NULL THEN $1 WHEN $2 = 1 THEN
> true ELSE false END
>
>
>
> $$ LANGUAGE sql IMMUTABLE;
>
>
>
>
>
> Thanks,
>
>
>
> Dave