Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

,--- You/Florian (Fri, 7 Oct 2011 03:21:23 +0200) ----*
| Sure. Deducing nullability isn't a hard problem, at least not if it's
| OK to simply say "nullable" if things get too complex.

Yes.

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

That's a good point.  But I'll do with what I manage to get.  I am
pretty sure that in my client's use, this is not going to be an issue.

And OTOH, I am not sure that other databases will give me a good
answer.  I'll play with them soon, out of technical curiosity.

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

Oh, great -- that's the second best answer I hoped for: just didn't
want to go down the expensive and not fool-proof way by mistake.  Had
to ask this list.

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

I've looked into the 'src/interfaces/libpq' and other parts of 'src'
more than once and suspect that I won't be able to find where to plug
this in correctly, even if I figure out a meaningful algorithm.

| Should you decide to do that,

Unlikely: in a couple of days I hope to have my implementation as I
described before, then there will be no need for our application to
wait for the desired PQfnullable function.  Besides, our application
has to work with any libpq.so.5, so no new PQ* function can be called.

I'd only venture to do it for the personal goal of contributing to
PostgreSQL.  Who knows, but unlikely -- a too high barrier to entry.

| 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

Thank you: this is all very valuable,

-- Alex -- alex-goncharov@comcast.net --