Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group