Re: Composite type evaluates to Null if any element is null

On Wed, Dec 17, 2008 at 12:23 PM, Andrew Gould
<andrewlylegould(at)gmail(dot)com> wrote:
> What are composite types used for? Do they allow you to search multiple
> fields for a value more easily?

A number of things really. Starting with 8.4, they can be used with
indexes and comparisons. so the list is growing. The main utility for
composite types though is passing arguments in and (especially)
returning arguments from functions.

All tables have a automatically generated composite type backing them, so:
select foo from foo; -- is legal
in 8.4, we can do:
create index foo_idx on foo((foo));

which optimizes things like:
select * from foo order by foo limit 5;
select foo from foo where foo = (1,2,3)::foo
if foo is defined as (int, int, int);

We can use composite types to get around subquery restrictions sometimes.
-- illegal, field list subquery must return one row, one column
select bar.* (select * from foo where bar_Id=bar.bar_id) from bar;

-- but this works:
select bar.*, (select foo from foo where bar_Id=bar.bar_id) from bar;

-- as above, with foo expanded:
select (bar).*, (foo).* from (select bar, (select foo from foo where
bar_Id=bar.bar_id) from bar) q;

-- starting with 8.3, we can make arrays of foo:
select array_accum(foo) from foo;

-- arrays can be nested:
create table barfoo(bar, foo[]);
select array(select (bar, (select array_accum(f) from f where f.bar_id
= bar.bar_id))::barfoo);

of course, if you were doing any of this in libpq, you absolutely
would want to be using libpqtypes ;-)

merlin