Обсуждение: Altering functions cast
How do I get this to work? create function nullif_bool(boolean) returns bool as ' select nullif(''$1'','''')::bool;' language sql; ??? Thank you. ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
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
We use NULLIF() for adding [1|0|null] according to the evalution of nullif('x','') into boolean columns. Where x is [1|0|null], which if it's an empty string (x='') then we add NULL cause NULLIF says if '' == '' then return NULL into boolean. An example wrapper function for this with an integer cast: CREATE FUNCTION nullif_always_int(text) RETURNS integer AS ' SELECT nullif($1,'''')::int; ' LANGUAGE SQL; I was hoping I could do something simliar with booleans if possible? ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Hey. I used your suggestion and it worked like a charm! The... CREATE FUNCTION nullif_bool(text) RETURNS boolean AS ' BEGIN RETURN nullif($1, ''''); END; ' LANGUAGE plpgsql IMMUTABLE STRICT; THANK YOU! --- Michael Fuhr <mike@fuhr.org> wrote: > 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 > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs