|
|
|
|
|
|
| Author |
Message |
Dan Strömberg
*nix forums beginner
Joined: 20 Jul 2006
Posts: 1
|
 Posted: Thu Jul 20, 2006 6:21 pm Post subject:
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match |
|
| Back to top |
|
 |
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Thu Jul 20, 2006 10:16 pm Post subject:
Re: Column info without executing query
|
|
|
=?ISO-8859-1?Q?Dan_Str=F6mberg?= <dan.stromberg@stockholm.bonet.se> writes:
| Quote: | 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match |
|
| Back to top |
|
|
Erik Jones
*nix forums beginner
Joined: 12 May 2006
Posts: 10
|
| Posted: Fri Jul 21, 2006 12:13 am Post subject:
Re: Column info without executing query
|
|
|
Dan Strömberg wrote:
| Quote: | 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)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly |
|
| Back to top |
|
|
Volkan YAZICI
*nix forums beginner
Joined: 13 Jun 2005
Posts: 31
|
| Posted: Fri Jul 21, 2006 9:12 am Post subject:
Re: Column info without executing query
|
|
|
On Jul 20 08:21, Dan Strömberg wrote:
| Quote: | 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.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Thomas Kellerer
*nix forums addict
Joined: 15 Apr 2005
Posts: 82
|
| Posted: Fri Jul 21, 2006 9:20 am Post subject:
Re: Column info without executing query
|
|
|
On 21.07.2006 11:12 Volkan YAZICI wrote:
| Quote: | 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Jacob Coby
*nix forums beginner
Joined: 06 Jun 2006
Posts: 8
|
| Posted: Fri Jul 21, 2006 1:02 pm Post subject:
Re: Column info without executing query
|
|
|
Volkan YAZICI wrote:
| Quote: | 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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Volkan YAZICI
*nix forums beginner
Joined: 13 Jun 2005
Posts: 31
|
| Posted: Fri Jul 21, 2006 1:14 pm Post subject:
Re: Column info without executing query
|
|
|
On Jul 21 09:02, Jacob Coby wrote:
| Quote: | 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Fri Jul 21, 2006 1:34 pm Post subject:
Re: Column info without executing query
|
|
|
On Fri, Jul 21, 2006 at 04:14:52PM +0300, Volkan YAZICI wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Jacob Coby
*nix forums beginner
Joined: 06 Jun 2006
Posts: 8
|
| Posted: Fri Jul 21, 2006 2:04 pm Post subject:
Re: Column info without executing query
|
|
|
Martijn van Oosterhout wrote:
| Quote: | 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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Volkan YAZICI
*nix forums beginner
Joined: 13 Jun 2005
Posts: 31
|
| Posted: Fri Jul 21, 2006 2:07 pm Post subject:
Re: Column info without executing query
|
|
|
On Jul 21 03:34, Martijn van Oosterhout wrote:
| Quote: | 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.
| Quote: | 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.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Fri Jul 21, 2006 2:25 pm Post subject:
Re: Column info without executing query
|
|
|
On Fri, Jul 21, 2006 at 05:07:08PM +0300, Volkan YAZICI wrote:
| Quote: | 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.
| Quote: | 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. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Tue Oct 07, 2008 2:41 pm | All times are GMT
|
|
Loans | Internet Advertising | Credit Cards | Buy Anything On eBay | Mortgages
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
