Polymorphic arguments and composite types

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Polymorphic arguments and composite types
Date: 2007-10-05 12:25:01
Message-ID: 1191587101.4223.344.camel@ebony.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 $$
declare
val integer[];
begin
select 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 $$
declare
val integer[];
begin
select 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2007-10-05 12:27:54 Re: First steps with 8.3 and autovacuum launcher
Previous Message Pavel Stehule 2007-10-05 11:30:13 Re: default_text_search_config