On Oct7, 2011, at 01:42 , Alex Goncharov wrote:
> Right: but for (most?) every SELECT, one can logically deduce whether
> it can be guaranteed that a given column will never have a NULL value.
> Since in a given SELECT, the result column are a combination of either
> other columns, or expressions, including literals.

Sure. Deducing nullability isn't a hard problem, at least not if it's
OK to simply say "nullable" if things get too complex.

> And in PostgreSQL, this could be done by combining
>  (1)   Oid PQftable(const PGresult *res, int column_number);
>  (2)   int PQftablecol(const PGresult *res, int column_number);
>  (3)   a SQL query of pg_attribute,attnotnull

That won't work. I'm pretty sure that you'll get the wrong answer
for queries involving OUTER joins, e.g.

  SELECT * FROM foo LEFT JOIN bar ON bar.foo_id = foo.foo_id

> I have not tried this yet, hesitating to walk into a monstrosity and
> hoping that there is some hidden way to get the information through
> one of
>  int PQfmod(const PGresult *res, int column_number);
>  int PQgetisnull(const PGresult *res, int row_number, int column_number);

Let me assure you that there's no "hidden way". The feature is simply

> Now, for this statement, I can easily identify non-nullable columns.
>  select
>       t1.nn1, -- guaranteed: not null
>       t1.ny1, -- nullable
>       t2.nn2, -- guaranteed: not null
>       t2.ny2  -- nullable
>  from t1, t1; 

Sure. So can I. But postgres can't, since nobody's implemented the necessary
algorithm so far. You're very welcome to produce a patch, though. Should you
decide to do that, I recommend that you discuss the design of this *before*
starting work (in a separate thread). Otherwise, you might discover objections
to the general approach, or even to the whole feature, only after you put
considerable effort into this.

best regards,
Florian Pflug

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to