Polymorphic arguments and composite types - Mailing list pgsql-hackers

I have a few questions from recent attempts to perform a join between
two tables, where one table has an integer array in it. Join is of the
form:

select ... from t1 where col1 = any (select col2 from t2);

Not sure whether these are bugs, intentional, incomplete functionality.
I've solved the problem, but not in a very straightforward manner.

Here's a simpler example that shows the problem I hit.

postgres=# \d c       Table "public.c"Column |   Type    | Modifiers 
--------+-----------+-----------col1   | integer   | col2   | integer[] | 

postgres=# select * from c;col1 | col2  
------+-------   1 | {1,2}
(1 row)

postgres=# select * from c where col1 = any ('{1,2}');col1 | col2  
------+-------   1 | {1,2}
(1 row)

postgres=# select * from c where col1 = any (col2);col1 | col2  
------+-------   1 | {1,2}
(1 row)

...which is fine on just one table, but I want to join...

postgres=# select * from c where col1 = any (select col2 from c);
ERROR:  operator does not exist: integer = integer[]
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

postgres=# select * from c where col1 = any (ARRAY(select col2 from c));
ERROR:  could not find array type for data type integer[]


Q1: Why not?? In the SELECT clause a sub-select returning a single
column is allowed, but it seems not here. Maybe a composite type issue?
Doesn't appear to be, since it knows type is integer[]

postgres=# select col1, (select col2 from c) as col2 from c;col1 | col2  
------+-------   1 | {1,2}
(1 row)

So we now try to create a function to do this instead...

postgres=# create function func() returns anyarray as $$
declareval integer[];
beginselect col2 into val from c;return val;
end;
$$ language plpgsql;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at least one
polymorphic argument.

Q2: Why does a function returning a polymorphic type have to have at
least one polymorphic argument? It's easy to create a function that
returns a polymorphic result yet has no linkage at all to the input.

postgres=# create function func(inval anyelement) returns anyarray as $$
declareval integer[];
beginselect col2 into val from c;return val;
end;
$$ language plpgsql;
CREATE FUNCTION

postgres=# select func(1);func  
-------{1,2}
(1 row)

postgres=# select * from c where col1 = any (select func(1));
ERROR:  operator does not exist: integer = integer[]
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

...same error, which is good news I suppose

postgres=# select * from c where col1 = any (func(1));col1 | col2  
------+-------   1 | {1,2}
(1 row)

Q3: Why is a composite type with just one attribute not the same type as
the attribute? We know this is possible in the SELECT list, but we don't
know its the same thing in other contexts.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com