Search Postgresql Archives

Re: Strange behavior with missing column in SQL function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Marcelo Lacerda <marceloslacerda@xxxxxxxxx> writes:
> Here's the code that reproduces the behavior:
> http://paste.debian.net/1035412/

For the archives' sake, the issue of concern here is what error
message to throw for

CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
RETURNS INTEGER AS $$
    SELECT myrow.c + myrow.b;
$$ LANGUAGE sql;

if mytable is a composite type that has no "c" column.

> I have already discussed this in the IRC channel but there doesn't seem to
> be a consensus on whether this is a bug here's a brief transcript of
> RhodiumToad's opinion:
>> this isn't new, goes back to 9.1 at least
>> basically, the error path in sql_fn_post_column_ref is a bit confused.
>> seeing r.c it tries to resolve it as parameter.field, fails, and rather
>> than reporting the error directly as being a missing field, it just returns
>> with the reference unresolved
>> then the outer parser code, having failed to resolve it as table.column
>> and having had the hook function not override it, reports it on the
>> assumption that it's a missing table
>> so it's probably been this way for as long as named parameters have
>> worked in language sql

I can't get real excited about changing this.  Yeah, it's obvious to a
human that the other error message wording would be more apropos here,
but there are nearby cases where that's not so.  So I'm afraid we'd
just be improving some cases by de-improving others.

The core of the problem is what about ambiguous references such as

CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
RETURNS INTEGER AS $$
    SELECT myrow.c + myrow.b FROM myrow;
$$ LANGUAGE sql;

where "myrow" is a table with a different set of column names from
"mytable".  The existing behavior for that is to seek the column name
in "myrow" (the table), failing that to seek it in the parameter,
and only to throw an error if both fail.  You could make a reasonable
argument that it shouldn't work like that --- having the query-local
table name mask the parameter entirely would likely be better.  But
I don't know if we can get away with changing that from a backwards
compatibility standpoint.  Even if we thought we could, the existing
hook definition doesn't support doing that; we'd need some other API.

If we did change this, then it'd be reasonable to tighten the error
message behavior; but as things stand, sql_fn_post_column_ref doesn't
know what's going on so it's not in a position to throw a reliable
error message.

			regards, tom lane




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux