Re: NOT NULL with CREATE TYPE

On Mon, Jun 8, 2009 at 2:18 PM, Jeff Davis<pgsql(at)j-davis(dot)com> wrote:
> On Sat, 2009-06-06 at 15:03 -0400, Merlin Moncure wrote:
>> sql functions are pretty inflexible...even with recent even with
>> recent advancements like varargs and default parameters they are
>> designed to do a very particular thing...and insert/update tend to be
>> fairly generic in how they operate.
>>
>
> I think Jean was using that as an example to show how attnotnull is
> sometimes invisible to the application, and the same would be true for a
> view.
>
> For instance, let's say you have:
>
> create table foo(i int not null);
> create view foo_v1 as select i from foo where i > 5;
> create view foo_v2 as select sum(i) as i from foo;
>
> Logically speaking, foo.i is not nullable, foo_v1.i is not nullable, but
> foo_v2.i _is_ nullable. The application has no good way to know that.

hm. maybe, try defining the return type from your function using
'create table' not 'create type':

create table foo (a int, b text not null);

create function get_foo() returns setof foo as ...

not sure if this works. if it does, it is yet another example of why
'create type as' is redundant and inferior to 'create table' for
purposes of creating composite types.

merlin