On Fri, Aug 26, 2005 at 12:22:27PM -0700, Matt A. wrote:
> How do I get this to work?
>
> create function nullif_bool(boolean) returns
> bool as ' select nullif(''$1'','''')::bool;' language
> sql;
If an empty string is a possible input then the argument type can't
be boolean because you'll get a syntax error before the function
is called:
CREATE FUNCTION nullif_bool(boolean) RETURNS boolean AS '
SELECT TRUE; -- for testing
' LANGUAGE sql;
SELECT nullif_bool('');
ERROR: invalid input syntax for type boolean: ""
The argument type should probably be text. But then you get a
different error, this time during function creation:
CREATE FUNCTION nullif_bool(text) RETURNS boolean AS '
SELECT nullif($1,'''')::boolean;
' LANGUAGE sql;
ERROR: cannot cast type text to boolean
One possible solution is to use PL/pgSQL instead of SQL and rely
on PL/pgSQL's automatic type conversion of the return value:
CREATE FUNCTION nullif_bool(text) RETURNS boolean AS '
BEGIN
RETURN nullif($1, '''');
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT nullif_bool('');
nullif_bool
-------------
(1 row)
SELECT nullif_bool('t');
nullif_bool
-------------
t
(1 row)
SELECT nullif_bool('f');
nullif_bool
-------------
f
(1 row)
Is that what you're looking for?
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
