pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with overloading the "coalesce" function
5+ messages / 3 participants
[nested] [flat]
* problem with overloading the "coalesce" function
@ 2006-03-06 10:18 Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 10:41 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Emil Rachovsky @ 2006-03-06 10:18 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with overloading the "coalesce" function
2006-03-06 10:18 problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
@ 2006-03-06 10:41 ` Richard Huxton <dev@archonet.com>
2006-03-06 11:13 ` Re: problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2006-03-06 10:41 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with overloading the "coalesce" function
2006-03-06 10:18 problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 10:41 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
@ 2006-03-06 11:13 ` Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 11:29 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Emil Rachovsky @ 2006-03-06 11:13 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with overloading the "coalesce" function
2006-03-06 10:18 problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 10:41 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
2006-03-06 11:13 ` Re: problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
@ 2006-03-06 11:29 ` Richard Huxton <dev@archonet.com>
2006-03-07 02:39 ` Re: problem with overloading the "coalesce" function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2006-03-06 11:29 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem with overloading the "coalesce" function
2006-03-06 10:18 problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 10:41 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
2006-03-06 11:13 ` Re: problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 11:29 ` Re: problem with overloading the "coalesce" function Richard Huxton <dev@archonet.com>
@ 2006-03-07 02:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-03-07 02:39 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Emil Rachovsky <zsevgymko@yahoo.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-07 02:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-06 10:18 problem with overloading the "coalesce" function Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 10:41 ` Richard Huxton <dev@archonet.com>
2006-03-06 11:13 ` Emil Rachovsky <zsevgymko@yahoo.com>
2006-03-06 11:29 ` Richard Huxton <dev@archonet.com>
2006-03-07 02:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox