Thread: Correcting Error message

Hi,

Consider following testcase,

CREATE TABLE footable(id int4, name varchar2(10));

CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10)
  RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL;

CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10)
  RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL;

SELECT (footable.*).foofunc FROM footable;
ERROR:  column footable.foofunc does not exist
LINE 1: SELECT (footable.*).foofunc FROM footable;
               ^

The error message thrown is seems to be wrong. When I dig into the code, I found in function ParseFuncOrColumn(), if we just add small condition it will throw correct error message. i.e. " function foofunc(footable) is not unique". I have made a slight change in code, which is throwing the correct error message now. The code changes are attached in the patch.

--
Piyush S Newe
Principal Engineer
EnterpriseDB
office: +91 20 3058 9500
www.enterprisedb.com

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

On Feb 26, 2010, at 3:30 , Piyush Newe wrote:

> Hi,
>
> Consider following testcase,
>
> CREATE TABLE footable(id int4, name varchar2(10));
>
> CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10)
>  RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL;
>
> CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10)
>  RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL;
>
> SELECT (footable.*).foofunc FROM footable;
> ERROR:  column footable.foofunc does not exist
> LINE 1: SELECT (footable.*).foofunc FROM footable;
>               ^

Is that calling syntax correct?  I'd think it should be:

SELECT foofunc(footable.*, 10) FROM footable;

Note there are two arguments to foofunc (in either version)

test=# SELECT version();                                                               version

--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL8.4.2 on i386-apple-darwin9.8.0, compiled by GCC i686- 
 
apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5493), 32-bit
(1 row)

test=# CREATE TABLE footable(id int4, name varchar(10));
CREATE TABLE
test=# INSERT INTO footable (id, name) VALUES (1, 'foo'), (2, 'bar');
INSERT 0 2
test=# CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10)
postgres-#  RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL;
CREATE FUNCTION
test=# CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10)
postgres-#  RETURNS integer AS $$ SELECT 456; $$ LANGUAGE SQL;
CREATE FUNCTION
test=# SELECT name, foofunc(footable.*, 10) FROM footable; name | foofunc
------+--------- foo  |     123 bar  |     123
(2 rows)

test=# SELECT name, foofunc(footable.*, 10.0) FROM footable; name | foofunc
------+--------- foo  |     456 bar  |     456
(2 rows)

In any event, I couldn't get your example to work on Postgres 8.4  
regardless due to the varchar2 type. Which version of Postgres are you  
using?

test=# CREATE TABLE footable(id int4, name varchar2(10));
ERROR:  type "varchar2" does not exist



Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> On Feb 26, 2010, at 3:30 , Piyush Newe wrote:
>> SELECT (footable.*).foofunc FROM footable;
>> ERROR:  column footable.foofunc does not exist

> Is that calling syntax correct?  I'd think it should be:
> SELECT foofunc(footable.*, 10) FROM footable;

He's relying on the f(x) === x.f syntactic equivalence, as per the
comments for ParseFuncOrColumn:*  For historical reasons, Postgres tries to treat the notations tab.col*  and col(tab)
asequivalent: if a single-argument function call has an*  argument of complex type and the (unqualified) function name
matches* any attribute of the type, we take it as a column projection.  Conversely*  a function of a single
complex-typeargument can be written like a*  column reference, allowing functions to act like computed columns.
 

or see the user-facing documentation near the end of section 34.4.2:
http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#AEN43797

It's still an unnecessarily awkward example though, as you could just
as well writeSELECT footable.foofunc FROM footable;

> Note there are two arguments to foofunc (in either version)

... and the example also relies on the presence of default arguments for
both functions.  This makes both of them match a single-argument call,
resulting in an ambiguous-function situation.  The proposed change
would cause it to actually throw an "ambiguous function" error.

I'm not very sure if the proposed change is an improvement or not.
The given message is 100% correct: there is no such column.  Now
if you were intending a function call, it would be more useful if it
complained about "ambiguous function" instead, but if you really just
typo'd a column name then that could be mighty confusing.  I'm inclined
to think that if you were intending a function call, you'd be most
likely to write it as a function call, especially if you didn't
understand why you were getting an error; and then you'd get the
message that was helpful for that case.  So I'm inclined to leave
the code alone.  It's a judgment call though, without a doubt.

It might help to make a decision if we saw a real-world case where
this happened and the other error message would be more desirable.
The example seems a bit contrived to me; who'd really create such a
pair of functions and then try to invoke them this way?
        regards, tom lane

On Feb 26, 2010, at 21:03 , Tom Lane wrote:

> Michael Glaesemann <grzm@seespotcode.net> writes:
>> On Feb 26, 2010, at 3:30 , Piyush Newe wrote:
>>> SELECT (footable.*).foofunc FROM footable;
>>> ERROR:  column footable.foofunc does not exist
>
>> Is that calling syntax correct?  I'd think it should be:
>> SELECT foofunc(footable.*, 10) FROM footable;
>
> He's relying on the f(x) === x.f syntactic equivalence, as per the
> comments for ParseFuncOrColumn:


>> Note there are two arguments to foofunc (in either version)
>
> ... and the example also relies on the presence of default arguments  
> for
> both functions.  This makes both of them match a single-argument call,
> resulting in an ambiguous-function situation.  The proposed change
> would cause it to actually throw an "ambiguous function" error.

Ah! Learned two new things. Thanks, Tom!

Michael Glaesemann
grzm seespotcode net

On Fri, Feb 26, 2010 at 7:12 PM, Michael Glaesemann
<grzm@seespotcode.net> wrote:
>
> In any event, I couldn't get your example to work on Postgres 8.4 regardless
> due to the varchar2 type. Which version of Postgres are you using?
>
> test=# CREATE TABLE footable(id int4, name varchar2(10));
> ERROR:  type "varchar2" does not exist
>

it;s probably postgres plus (the enterprisedb fork), because varchar2
it's an oracle invention

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> On Fri, Feb 26, 2010 at 7:12 PM, Michael Glaesemann <grzm@seespotcode.net> wrote:
>> In any event, I couldn't get your example to work on Postgres 8.4 regardless
>> due to the varchar2 type. Which version of Postgres are you using?
>> 
>> test=# CREATE TABLE footable(id int4, name varchar2(10));
>> ERROR:  type "varchar2" does not exist

> it;s probably postgres plus (the enterprisedb fork),

Yeah, particularly given the OP's address ;-).  The example goes through
fine in standard Postgres if you use varchar, or indeed any other
datatype.
        regards, tom lane