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
unsupported.
> 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