Обсуждение: problem with overloading the "coalesce" function
Hi, I am trying to overload the "coalesce" function to accept an integer and a string. Here it is : CREATE OR REPLACE FUNCTION "coalesce"(a int4, b "varchar") RETURNS "varchar" AS $BODY$ begin if (a is null ) then return b; else return cast(a as varchar(15)); end if; end $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION "coalesce"(a int4, b "varchar") OWNER TO postgres; I have added it to pg_catalog, but still I cant't use it, I get an error on the second parameter, apparently the function gets lost at some point. Any additional steps I need to complete? Thanks in advance, Emil __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Emil Rachovsky wrote: > > Hi, > I am trying to overload the "coalesce" function to > accept an integer and a string. Here it is : > > CREATE OR REPLACE FUNCTION "coalesce"(a int4, b > "varchar") > RETURNS "varchar" AS ... > I have added it to pg_catalog, but still I cant't use > it, I get an error on the second parameter What is the error? -- Richard Huxton Archonet Ltd
--- Richard Huxton <dev@archonet.com> wrote: > Emil Rachovsky wrote: > > > > Hi, > > I am trying to overload the "coalesce" function to > > accept an integer and a string. Here it is : > > > > CREATE OR REPLACE FUNCTION "coalesce"(a int4, b > > "varchar") > > RETURNS "varchar" AS > ... > > I have added it to pg_catalog, but still I cant't > use > > it, I get an error on the second parameter > > What is the error? The error is : invalid input syntax for integer That is,it expects an integer as a second parameter, since the first is an integer. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Emil Rachovsky wrote: > > --- Richard Huxton <dev@archonet.com> wrote: > >> Emil Rachovsky wrote: >>> Hi, >>> I am trying to overload the "coalesce" function to >>> accept an integer and a string. Here it is : >>> >>> CREATE OR REPLACE FUNCTION "coalesce"(a int4, b >>> "varchar") >>> RETURNS "varchar" AS >> ... >>> I have added it to pg_catalog, but still I cant't >> use >>> it, I get an error on the second parameter >> What is the error? > > The error is : invalid input syntax for integer > That is,it expects an integer as a second parameter, > since the first is an integer. Hmm - looking at the source (and \df in psql) it seems the basic problem is that COALESCE() isn't a function. It has its own code in the parser and its own expression-node. So - your function never gets called because the parser sees coalesce() and doesn't build a function - it builds a coalesce-expression. It should work fine if you rename your function of course. HTH -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: > Hmm - looking at the source (and \df in psql) it seems the basic problem > is that COALESCE() isn't a function. If it were an ordinary function, it couldn't satisfy the property of not evaluating "unused" arguments ... regards, tom lane