On Wed, Dec 17, 2008 at 12:23 PM, Andrew Gould <andrewlylegould@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
