Column info without executing query

=?ISO-8859-1?Q?Dan_Str=F6mberg?= <dan.stromberg@stockholm.bonet.se> writes:

I would like to know if it is possible to find out the datatypes in the
resultset of a query or a set returning
function without actually executing them ?

You could always do "SELECT ...whatever ... LIMIT 0". Also, at the
protocol level there's Parse/Describe Statement, but whatever client
library you're using may not expose that usefully (I don't think libpq
does for instance).

regards, tom lane

Dan Str�mberg wrote:

Hi List !

I would like to know if it is possible to find out the datatypes in
the resultset of a query or a set returning
function without actually executing them ?

Well for result sets of queries on tables there is always the
pg_attribute catalog table.

See:
http://www.postgresql.org/docs/8.1/interactive/catalog-pg-attribute.html

--
erik jones <erik@myemma.com>
software development
emma(r)

On Jul 20 08:21, Dan Str�mberg wrote:

I would like to know if it is possible to find out the datatypes in the
resultset of a query or a set returning
function without actually executing them ?

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet. However, here's a
patch that adds Describe functionality for Prepared Statements and
Cursors to libpq:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01281.php

Regards.

On 21.07.2006 11:12 Volkan YAZICI wrote:

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet.

I think if you run a query like the suggested one (or SELECT ... WHERE
1=2) the JDBC API will provide the necessary information via
ResultSetMetaData

Thomas

Volkan YAZICI wrote:

On Jul 20 08:21, Dan Str�mberg wrote:

I would like to know if it is possible to find out the datatypes in the
resultset of a query or a set returning
function without actually executing them ?

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet. However, here's a
patch that adds Describe functionality for Prepared Statements and
Cursors to libpq:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01281.php

PHP supports it with the pg_field_type() function.

On Jul 21 09:02, Jacob Coby wrote:

Volkan YAZICI wrote:

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet. However, here's a
patch that adds Describe functionality for Prepared Statements and
Cursors to libpq:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01281.php

PHP supports it with the pg_field_type() function.

PHP uses libpq in the background to communicate with the server. How
can you wait PHP to support a feature that's not supported by libpq?
Furtheremore, pg_field_type() queries system catalogs to collect
information which is quite different than Describe functionality.

Regards.

On Fri, Jul 21, 2006 at 04:14:52PM +0300, Volkan YAZICI wrote:

On Jul 21 09:02, Jacob Coby wrote:

Volkan YAZICI wrote:

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet. However, here's a
patch that adds Describe functionality for Prepared Statements and
Cursors to libpq:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01281.php

PHP supports it with the pg_field_type() function.

PHP uses libpq in the background to communicate with the server. How
can you wait PHP to support a feature that's not supported by libpq?
Furtheremore, pg_field_type() queries system catalogs to collect
information which is quite different than Describe functionality.

Really, I would have thought the PHP function would map directly to the
libpq PQftype() function. Although libpq returns the OID whereas the
PHP function returns the type. But I don't think that's what the
original user asked for given you need a ResultSet first.

This is kind of related to the "feature" of libpq that it won't give
you a resultset until the query is complete.

Note: this isn't entirely true, you can do a PQgetResult on an
asyncronous query while it is not yet finished and look at the partial
resultset. I used this in my mvcctest program to be able to track
exactly how far info a resultset it blocked. I wonder if you could send
the query asyncronously and then consume input until you get the
header. At least it'll give you the info before running the whole
query... It doesn't give you it at prepare stage though.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

On Fri, Jul 21, 2006 at 04:14:52PM +0300, Volkan YAZICI wrote:

On Jul 21 09:02, Jacob Coby wrote:

Volkan YAZICI wrote:

As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API supports this yet. However, here's a
patch that adds Describe functionality for Prepared Statements and
Cursors to libpq:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg01281.php

PHP supports it with the pg_field_type() function.

PHP uses libpq in the background to communicate with the server. How
can you wait PHP to support a feature that's not supported by libpq?
Furtheremore, pg_field_type() queries system catalogs to collect
information which is quite different than Describe functionality.

Really, I would have thought the PHP function would map directly to the
libpq PQftype() function. Although libpq returns the OID whereas the
PHP function returns the type. But I don't think that's what the
original user asked for given you need a ResultSet first.

It uses PQftype() to get the oid and then queries the pg_type table to
map the oid to the typname.

On Jul 21 03:34, Martijn van Oosterhout wrote:

Really, I would have thought the PHP function would map directly to the
libpq PQftype() function. Although libpq returns the OID whereas the
PHP function returns the type. But I don't think that's what the
original user asked for given you need a ResultSet first.

Maybe, it's time to consider that Describe functionality for libpq
again. Lot's of applications currently rely on libpq to communicate
with the server. And IMHO, any application will be happy to benefit from
a function to query portal headers without requiring a whole result set.

This is kind of related to the "feature" of libpq that it won't give
you a resultset until the query is complete.

... how far info a resultset it blocked. I wonder if you could send
the query asyncronously and then consume input until you get the
header. At least it'll give you the info before running the whole
query... It doesn't give you it at prepare stage though.

AFAICS, that's not possible with current parsing capabilities. See
related lines in

fe-protocol3.c:pqParseInput3()
102 /*
103 * Can't process if message body isn't all here yet.
104 */

But, IMNSHO, we can modify parsing functionality to process message
parts step by step. For instance, in the current behaviour when we
receive a T, D, D, ... message, libpq won't attempt to process data
until it receives whole data chunk. But with some modification on the
parser side, we can make it process data in such a way:

Recv: T
Proc: T
Recv: D
Proc: D
...

But in this case, some advanced function routines must be written to
access conn->result in a hardcoded way under strict control. Because,
PQgetReesult() won't work properly till it receives whole result set.
Furthermore, similar modifications on the PQgetResult() will cause
serious compatibility issues. Also, mentioned routines (to access
conn->result while receive-and-parse'ing at the same time) will make
it possible to receive partial results without using cursors.

Regards.

On Fri, Jul 21, 2006 at 05:07:08PM +0300, Volkan YAZICI wrote:

AFAICS, that's not possible with current parsing capabilities. See
related lines in

fe-protocol3.c:pqParseInput3()
102 /*
103 * Can't process if message body isn't all here yet.
104 */

But, IMNSHO, we can modify parsing functionality to process message
parts step by step. For instance, in the current behaviour when we
receive a T, D, D, ... message, libpq won't attempt to process data
until it receives whole data chunk. But with some modification on the
parser side, we can make it process data in such a way:

Actually, you're wrong. It processes the T as it comes in, and then
each D as it comes in. "message body" in this case refers to a single
'T' or 'D' record, not the entire query result.

But in this case, some advanced function routines must be written to
access conn->result in a hardcoded way under strict control. Because,
PQgetReesult() won't work properly till it receives whole result set.

Did you see my comment about get partial result sets from libpq. for
asyncronous queries you can run PQftype as soon as you've received and
parsed the T record, you don't actually have to have received any data
yet... See pqPrepareAsyncResult().

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Jul 21 04:25, Martijn van Oosterhout wrote:

On Fri, Jul 21, 2006 at 05:07:08PM +0300, Volkan YAZICI wrote:

AFAICS, that's not possible with current parsing capabilities. See
related lines in

fe-protocol3.c:pqParseInput3()
102 /*
103 * Can't process if message body isn't all here yet.
104 */

But, IMNSHO, we can modify parsing functionality to process message
parts step by step. For instance, in the current behaviour when we
receive a T, D, D, ... message, libpq won't attempt to process data
until it receives whole data chunk. But with some modification on the
parser side, we can make it process data in such a way:

Actually, you're wrong. It processes the T as it comes in, and then
each D as it comes in. "message body" in this case refers to a single
'T' or 'D' record, not the entire query result.

That's what I was suspicious at the beginning, whether "message body"
refers to a single record or an entire query. But I forget to check
while typing. :)

But in this case, some advanced function routines must be written to
access conn->result in a hardcoded way under strict control. Because,
PQgetReesult() won't work properly till it receives whole result set.

Did you see my comment about get partial result sets from libpq. for
asyncronous queries you can run PQftype as soon as you've received and
parsed the T record

We can run PQftype() on what, conn->result? (We can't use a PGresult
will be returned from a PQgetResult() in here; because, AFAIK, after
a getRowDescriptions(), PQgetResult() still won't return a PGresult
because of conn->asyncStatus is still PGASYNC_BUSY.) That's why I
proposed generic methods to give user the conn->result access under
control.

Also, how can a client can realize whether T message is parsed
completely?

Regards.

My question is a bit off the path that you're taking with this issues, but I'd like to add a twist to the discussion -- to meet my needs of course :)

Is it possible to simply retrieve Column Type (Numeric, Varchar, Int,...) data for a given table using ecpg?

The reason I ask is that when I write in C/C++ and do a memset to NULL ('\0') on a struct to use as an Input for a table, if I do not receive a value for a Numeric data type from the calling program, I get this error:

ERROR: -400 'invalid input syntax for type numeric: ""'

I'm guessing this error is associated with the fact that the Struct Member is NULL, and it doesn't like that.

So, I get around the "problem" all right, but I'd prefer to not have to hard code this "fix" in my code for each Numeric field. I'd prefer grabbing the "Format" of the table and then only calling this "fix" function when the column I'm inserting into is of type Numeric.

But, I don't want to have any associated Query or Select that was executed first, as this Thread seems to be mostly dealing with.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van Oosterhout
Sent: Friday, July 21, 2006 9:25 AM
To: Volkan YAZICI
Cc: Jacob Coby; Dan Strömberg; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Column info without executing query

On Fri, Jul 21, 2006 at 05:07:08PM +0300, Volkan YAZICI wrote:

AFAICS, that's not possible with current parsing capabilities. See
related lines in

fe-protocol3.c:pqParseInput3()
102 /*
103 * Can't process if message body isn't all here yet.
104 */

But, IMNSHO, we can modify parsing functionality to process message
parts step by step. For instance, in the current behaviour when we
receive a T, D, D, ... message, libpq won't attempt to process data
until it receives whole data chunk. But with some modification on the
parser side, we can make it process data in such a way:

Actually, you're wrong. It processes the T as it comes in, and then each D as it comes in. "message body" in this case refers to a single 'T' or 'D' record, not the entire query result.

But in this case, some advanced function routines must be written to
access conn->result in a hardcoded way under strict control. Because,
PQgetReesult() won't work properly till it receives whole result set.

Did you see my comment about get partial result sets from libpq. for asyncronous queries you can run PQftype as soon as you've received and parsed the T record, you don't actually have to have received any data yet... See pqPrepareAsyncResult().

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Fri, Jul 21, 2006 at 05:47:32PM +0300, Volkan YAZICI wrote:

Did you see my comment about get partial result sets from libpq. for
asyncronous queries you can run PQftype as soon as you've received and
parsed the T record

We can run PQftype() on what, conn->result? (We can't use a PGresult
will be returned from a PQgetResult() in here; because, AFAIK, after
a getRowDescriptions(), PQgetResult() still won't return a PGresult
because of conn->asyncStatus is still PGASYNC_BUSY.) That's why I
proposed generic methods to give user the conn->result access under
control.

Well actually, that's exactly what you do. If you set libpq to
non-blocking mode and call PQgetResult() on an async query, it will in
fact give a result set that PQftype() works on.

Note that in this mode some functions sometimes return odd results. For
example, you only know that the query has finished executing when
PQgetResult returns a fatal error. Still, I and others have used this
technique successfully. See my mvcctest program:

http://svana.org/kleptog/pgsql/mvcctest.tar.gz

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

I found pg_prog.prorettype in the system catalogs , is it possible to
use that to find the resultset datatypes of a
set returning function ?

Anyway , maybe the odbc driver will work by using SQLDescribecol()
without executing it ?!
I found something in the archives where someone suggests that it has
worked before at least
http://archives.postgresql.org/pgsql-odbc/2004-09/msg00046.php

//Dan