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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
