Обсуждение: Composite type evaluates to Null if any element is null

I have just re-read chapter 8.15 of the 8.3 manual and I am
understanding why a composite type would evaluate to NUll if any of it
elements are null.

Can anyone explain this behavior?  To me if I have a composite type
column and there are some values in it, its incomplete, yes, but not
null.

Thanks for the feedback.


Ketema J. Harris
www.ketema.net
ketema@ketema.net
ketemaj on iChat

On Wed, Dec 17, 2008 at 11:09 AM, Ketema Harris <ketema@ketema.net> wrote:
> I have just re-read chapter 8.15 of the 8.3 manual and I am understanding
> why a composite type would evaluate to NUll if any of it elements are null.
>
> Can anyone explain this behavior?  To me if I have a composite type column
> and there are some values in it, its incomplete, yes, but not null.

can you be a little more specific?

postgres=# create table ct(a int, b int, c int);
CREATE TABLE
postgres=#
postgres=# select (1, null, 2)::ct;
  row
--------
 (1,,2)
(1 row)

postgres=# select (1, null, 2)::ct is null;
 ?column?
----------
 f
(1 row)

note, there is significant behavior change in the way composite type
works in comparison purposes for 8.4, which I am using.

merlin

On Wed, Dec 17, 2008 at 12:23 PM, Andrew Gould
<andrewlylegould@gmail.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