Thread: Column info without executing query
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 ? I know there is something like that in MS SQL Server where you can use the SET FMTONLY option and only get the column information back from the query . Would something like that be possible in postgres ? Regards Dan
=?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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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 --
tomeet 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
Numericfield. I'd prefer grabbing the "Format" of the table and then only calling this "fix" function when the column
I'minserting 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
dealingwith.
-----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
refersto 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'vereceived 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/
> From each according to his ability. To each according to his ability to litigate.
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.
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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