table and column information from cursor?

John DeSoi <desoi@pgedit.com> writes:

I was wondering if there is some way I'm missing to get the table and
column information from a cursor. If I fetch from a cursor, the table
OID and column number values are 0 in the row description. If I execute
the same query directly without a cursor, the row description has the
correct values for table OID and column number. I'm using the v3
protocol via a socket with PostgreSQL 8.0.

This looks a bit messy to fix. The information exists in the Portal
where the cursor itself is stored, but the FETCH is executed in a
different Portal that does not have it. If you trace through the code
you find the immediate failure in printtup_startup, which only knows how
to dig the targetlist out of a plain SELECT Query:

/*
* If this is a retrieve, and we are supposed to emit row
* descriptions, then we send back the tuple descriptor of the tuples.
*/
if (operation == CMD_SELECT && myState->sendDescrip)
{
List *targetlist;

if (portal->strategy == PORTAL_ONE_SELECT)
targetlist = ((Query *) linitial(portal->parseTrees))->targetList;
else
targetlist = NIL;

SendRowDescriptionMessage(typeinfo, targetlist, portal->formats);
}

We could probably kluge this code to be able to recognize a FETCH as
well, and then somehow dig the targetlist out of the cursor's portal,
but it sounds pretty ugly ... and it surely wouldn't scale to other
sorts of utility statements that return tuples, although offhand I can't
think of any for which there'd be a clear connection of returned columns
to table columns.

One way to fix this would be to pass the cursor's Portal, and not the
FETCH command's Portal, to the DestReceiver --- not sure how messy that
would be. Otherwise it seems like we need a cleaner way of decorating a
Portal with original-column info, so that a FETCH command could arrange
to copy the info into the Portal where it's executing.

More generally, it may be that attaching the original-column info to
targetlist entries was a bad idea in the first place, and we ought to
keep it someplace else ... not sure where would be better though.

Thoughts anyone?

regards, tom lane

John DeSoi <desoi@pgedit.com> writes:

I was wondering if there is some way I'm missing to get the table and
column information from a cursor. If I fetch from a cursor, the table
OID and column number values are 0 in the row description. If I execute
the same query directly without a cursor, the row description has the
correct values for table OID and column number. I'm using the v3
protocol via a socket with PostgreSQL 8.0.

Fixed in CVS tip. Turned out that prepared statements had the same
issue, that is in

PREPARE foo AS SELECT ...;
EXECUTE foo;

the row description returned by EXECUTE wouldn't tell you where the
columns came from.

regards, tom lane