Horton
Menu

Thread

  1. Correcting Error message

    Piyush Newe <piyush.newe@enterprisedb.com> — 2010-02-26T08:30:05Z

    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.

  2. Re: Correcting Error message

    Michael Glaesemann <grzm@seespotcode.net> — 2010-02-27T00:12:51Z

    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
--------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.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

  3. Re: Correcting Error message

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-02-27T02:03:08Z

    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) as equivalent: 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-type argument 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 write
	SELECT 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

  4. Re: Correcting Error message

    Michael Glaesemann <grzm@seespotcode.net> — 2010-02-27T02:44:09Z

    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

  5. Re: Correcting Error message

    Jaime Casanova <jcasanov@systemguards.com.ec> — 2010-02-27T06:08:39Z

    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

  6. Re: Correcting Error message

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-02-27T06:16:34Z

    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