Thread: NOT NULL with CREATE TYPE

Dear list,

Here's the situation: I want to create a functional API to a Postgresql
database, in such a way that instead of issuing raw SQL commands (SELECT,
INSERT, etc), the client will only invoke functions from this API.

For example, to get all people in the database, the client will invoke
function "SELECT * FROM get_people()" instead of manually doing a SELECT
over the 'people' table:

create table people (id serial primary key, name text not null, age int4 not null);
create type result as (name text, age int4);
create function get_people() returns setof result as $$ select name, age from people $$ language sql;

But here's the problem: I want the client side library to check the
attnotnull field from pg_attribute to determine if it may be null.
However, in a user defined type like 'result' above, all the fields
are marked as possibly being null.

I have tried the following, but it's not accepted:
create type result as (name text not null, age int4 not null);

I also tried manually setting the attnotnull value in result to TRUE,
but it doesn't work as expected.

I also thought of creating a dummy table for the return results, but
I'm afraid the performance penalty might be too great.  Could you give
me some help in solving this problem?

Thank you!
Jean

Jean Hoderd <jhoderd@yahoo.com> writes:
> I have tried the following, but it's not accepted:
> create type result as (name text not null, age int4 not null);

Frankly, the notion that a "not null" condition might be associated with
a SQL data type is simply a bad idea.  The SQL committee let this happen
for domains in an episode of monumentally fuzzy thinking, but you don't
want to do it.  Consider

    create table t1 (id1 int, f1 int);
    create table t2 (id2 int, f2 mynotnulltype);
    select * from t1 left join t2 on id1=id2;

What is the datatype of the f2 column of the output?  mynotnulltype,
presumably.  Now what do you do about t1 rows that have no match in
t2?  You can either emit null-extended rows, thus producing null
values in a mynotnulltype column, or throw an error, which isn't too
appetizing either --- you just rendered outer joins useless.

With a not null domain, it is at least possible to finesse this by
deciding that the join output column should be considered to be
of the domain's base type.  If "not null" is hardwired into the
type definition, there's no way out.

So I don't recommend you try to do this.  What is the actual problem you
are trying to solve?  Why do you want the client library to be concerned
with attnotnull at all?

> For example, to get all people in the database, the client will invoke
> function "SELECT * FROM get_people()" instead of manually doing a SELECT
> over the 'people' table:

... or even more to the point, why do you think the above is a good idea
to begin with?  It looks more like the kind of bad design that is
frequently committed by people who basically don't like SQL, and try to
ensure that no one else will like it either.

            regards, tom lane

Hi,

> So I don't recommend you try to do this.  What is the actual problem
> you are trying to solve?  Why do you want the client library to be
> concerned with attnotnull at all?

In general the client library needs to check attnotnull to make sure
that the client is not making a type mistake.  If for example a field
is "int null", but the client program treats it as "int not null" (ie,
forgetting to check for the null case), then the client program is
wrong and the compiler should issue a warning.  This is basic, essential,
functionality.  But I'm curious: how else could this be done?...

> ... or even more to the point, why do you think the above is a good
> idea to begin with?  It looks more like the kind of bad design that
> is frequently committed by people who basically don't like SQL, and
> try to ensure that no one else will like it either.

The above example was absurdly simple, but in the real world the query
is complex enough that instead of being just a SELECT is actually the
return of a PL/PGSQL function.  I just want a way to tell the client
which fields from the return type are actually, really, nullable...

Any other ideas?

Cheers,
Jean

On Fri, 2009-06-05 at 10:58 -0700, Jean Hoderd wrote:
> The above example was absurdly simple, but in the real world the query
> is complex enough that instead of being just a SELECT is actually the
> return of a PL/PGSQL function.  I just want a way to tell the client
> which fields from the return type are actually, really, nullable...

The way you are approaching this problem is understandable. You're
thinking of NULL as just an extra value in the domain of the type (and
therefore can be restricted by a type constraint), but that is not true
in SQL. You may think this approach is good or you may think it's bad,
but NULL permeates SQL at many levels, and can't merely be ignored.

In general, in SQL, NULLs can be produced in several ways even if every
column in your database is declared NOT NULL and you never specify a
NULL in any query. For instance, aggregates produce NULLs when there are
no input rows (COUNT is an exception), and (as Tom pointed out) OUTER
JOIN produces NULLs when there is no matching row on the other side.

I believe this fact foils the kind of checks you intend to do in the
general case, although you may be able to work around it creatively for
your specific situation. If you are interested in such a workaround,
provide a few more details and someone will probably have some ideas for
you.

The best I can say right now is that the client always needs to check
for NULL unless it somehow knows that NULL can't be produced.

Regards,
    Jeff Davis

On Fri, Jun 5, 2009 at 1:58 PM, Jean Hoderd<jhoderd@yahoo.com> wrote:
>
> Hi,
>
>> So I don't recommend you try to do this.  What is the actual problem
>> you are trying to solve?  Why do you want the client library to be
>> concerned with attnotnull at all?
>
> In general the client library needs to check attnotnull to make sure
> that the client is not making a type mistake.  If for example a field
> is "int null", but the client program treats it as "int not null" (ie,
> forgetting to check for the null case), then the client program is
> wrong and the compiler should issue a warning.  This is basic, essential,
> functionality.  But I'm curious: how else could this be done?...
>
>> ... or even more to the point, why do you think the above is a good
>> idea to begin with?  It looks more like the kind of bad design that
>> is frequently committed by people who basically don't like SQL, and
>> try to ensure that no one else will like it either.
>
> The above example was absurdly simple, but in the real world the query
> is complex enough that instead of being just a SELECT is actually the
> return of a PL/PGSQL function.  I just want a way to tell the client
> which fields from the return type are actually, really, nullable...

I agree with Tom...here is what I see as good reasons to get the app
to call functions:

*) chain together sequence of statements with dependant inputs and outputs
*) interface to a library hooked to the database that you dont want to
expose to the app for some reason, like dblink, or advisory locks
*) it's difficult or problematic to change/recompile the client side code

OTOH, if you are simply hiding one-liner select statements, you should
strongly consider using views instead of functions where possible.
The internal workings of views are more exposed to the planner at
query time and you are not forcing a particular method of invocation
to the app.  If you are wrapping updates and inserts (especially
updates), you are likely just creating work for yourself...consider
moving data dependent code into triggers.

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.

merlin

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.

Regards,
    Jeff Davis

On Mon, Jun 8, 2009 at 2:18 PM, Jeff Davis<pgsql@j-davis.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