Thread: BUG #13652: Function names as a table prefiex by underscore, confused with array

The following bug has been logged on the website:

Bug reference:      13652
Logged by:          Maris Radu
Email address:      marisradu@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Ubuntu Server 14.04.1
Description:

Creating a method "_c()" as:
create or replace function _c(text) returns text as $$
    select $1;
$$ language sql immutable;

and a table "c" as:
create table c (id int);

Select by _c(text) returns unexpected error:

# select _c('text');
ERROR:  malformed array literal: "text"
LINE 1: select _c('text');
                  ^
DETAIL:  Array value must start with "{" or dimension information.


Dropping the table or renaming the function to solves the issue.

Expecting: The query to run normally, or an error when creating the function
or the table if the function was defined first.

marisradu@gmail.com writes:
> Creating a method "_c()" as:
> create or replace function _c(text) returns text as $$
>     select $1;
> $$ language sql immutable;

> and a table "c" as:
> create table c (id int);

> Select by _c(text) returns unexpected error:

> # select _c('text');
> ERROR:  malformed array literal: "text"
> LINE 1: select _c('text');
>                   ^
> DETAIL:  Array value must start with "{" or dimension information.

The reason that happens is that the internal name of the array type c[]
is "_c", and the order of search for ambiguous function names is such
that a function-style cast interpretation will be selected over an
actual function; see
http://www.postgresql.org/docs/9.4/static/typeconv-func.html
particularly step 3.

I'm afraid that there's not much we can do about this without breaking
cases that people are accustomed to have working.

            regards, tom lane

On Tuesday, September 29, 2015, <marisradu@gmail.com
<javascript:_e(%7B%7D,'cvml','marisradu@gmail.com');>> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13652
> Logged by:          Maris Radu
> Email address:      marisradu@gmail.com
> PostgreSQL version: 9.4.4
> Operating system:   Ubuntu Server 14.04.1
> Description:
>
> Creating a method "_c()" as:
> create or replace function _c(text) returns text as $$
>     select $1;
> $$ language sql immutable;
>
> and a table "c" as:
> create table c (id int);
>
> Select by _c(text) returns unexpected error:
>
> # select _c('text');
> ERROR:  malformed array literal: "text"
> LINE 1: select _c('text');
>                   ^
> DETAIL:  Array value must start with "{" or dimension information.
>
>
> Dropping the table or renaming the function to solves the issue.
>
> Expecting: The query to run normally, or an error when creating the
> function
> or the table if the function was defined first.
>
>
Not a bug.

It's unfortunate that such an implementation detail is exposed in this
situation but the name is still valid if you place it in double-quotes (I
think...) so making it fail at any other time seems unnecessarily strict.
Maybe a hint would be in order but otherwise I'm not seeing much here that
would be worth significant effort to correct even ignoring the potential
breakage which Tom aludes to.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, September 29, 2015, <marisradu@gmail.com
> <javascript:_e(%7B%7D,'cvml','marisradu@gmail.com');>> wrote:
>> Select by _c(text) returns unexpected error:
>> # select _c('text');
>> ERROR:  malformed array literal: "text"

> It's unfortunate that such an implementation detail is exposed in this
> situation but the name is still valid if you place it in double-quotes (I
> think...) so making it fail at any other time seems unnecessarily strict.

Actually the way to get this to work as desired is to cast the argument
to the right type:

select _c('text'::text);

Then you get a match at step 2 in the resolution sequence ("exact match
of argument types"), so the user-defined function wins out over the cast
interpretation.

            regards, tom lane

I agree with you guys, especially keeping backwards compatibility, but it
worth letting you know about it in case there's an easy fix (as Craig
suggested in
http://stackoverflow.com/questions/32840450/function-defined-as-ctext-error-about-expecting-array
).

My suggestion is to add an example of this behavior in the docs:
http://www.postgresql.org/docs/9.4/static/typeconv-func.html (the one Tom
pointed to), with a little explanation about why this happens and how to
avoid it.

Regards,
Radu M.



On Tue, Sep 29, 2015 at 6:50 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Tuesday, September 29, 2015, <marisradu@gmail.com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      13652
>> Logged by:          Maris Radu
>> Email address:      marisradu@gmail.com
>> PostgreSQL version: 9.4.4
>> Operating system:   Ubuntu Server 14.04.1
>> Description:
>>
>> Creating a method "_c()" as:
>> create or replace function _c(text) returns text as $$
>>     select $1;
>> $$ language sql immutable;
>>
>> and a table "c" as:
>> create table c (id int);
>>
>> Select by _c(text) returns unexpected error:
>>
>> # select _c('text');
>> ERROR:  malformed array literal: "text"
>> LINE 1: select _c('text');
>>                   ^
>> DETAIL:  Array value must start with "{" or dimension information.
>>
>>
>> Dropping the table or renaming the function to solves the issue.
>>
>> Expecting: The query to run normally, or an error when creating the
>> function
>> or the table if the function was defined first.
>>
>>
> Not a bug.
>
> It's unfortunate that such an implementation detail is exposed in this
> situation but the name is still valid if you place it in double-quotes (I
> think...) so making it fail at any other time seems unnecessarily strict.
> Maybe a hint would be in order but otherwise I'm not seeing much here that
> would be worth significant effort to correct even ignoring the potential
> breakage which Tom aludes to.
>
> David J.
>