pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting datatype before SELECT
11+ messages / 9 participants
[nested] [flat]
* Getting datatype before SELECT
@ 1998-09-29 17:05 Glenn Sullivan <glenn.sullivan@nmr.varian.com>
0 siblings, 4 replies; 11+ messages in thread
From: Glenn Sullivan @ 1998-09-29 17:05 UTC (permalink / raw)
To: pgsql-sql@hub.org
In my C code which communicates with the Postgres database,
I have the need to determine the datatype of a column, before
I have done a SELECT command. I have the name of the column,
but I cannot seem to figure out how to get the datatype information
until after I have done a SELECT. Then I can call PQfnumber() and
PQftype() to get the type. Does anyone know how to do this?
Thanks,
Glenn
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-29 21:28 David Hartwig <daveh@insightdist.com>
parent: Glenn Sullivan <glenn.sullivan@nmr.varian.com>
3 siblings, 0 replies; 11+ messages in thread
From: David Hartwig @ 1998-09-29 21:28 UTC (permalink / raw)
To: Glenn Sullivan <glenn.sullivan@nmr.varian.com>; +Cc: pgsql-sql@hub.org
Glenn Sullivan wrote:
> In my C code which communicates with the Postgres database,
> I have the need to determine the datatype of a column, before
> I have done a SELECT command. I have the name of the column,
> but I cannot seem to figure out how to get the datatype information
> until after I have done a SELECT. Then I can call PQfnumber() and
> PQftype() to get the type. Does anyone know how to do this?
-- will give you the attribute name
SELECT pg_type.typname FROM pg_class, pg_type, pg_attribute WHERE
pg_class.relname = 'your_table' AND
pg_attribute.attname = 'your_attribute' AND
pg_class.oid = pg_attribute.attrelid AND
pg_attribute.atttypid = pg_type.oid;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-30 02:10 D'Arcy J.M. Cain <darcy@druid.net>
parent: Glenn Sullivan <glenn.sullivan@nmr.varian.com>
3 siblings, 0 replies; 11+ messages in thread
From: darcy@druid.net @ 1998-09-30 02:10 UTC (permalink / raw)
To: glenn.sullivan@nmr.varian.com; +Cc: pgsql-sql@hub.org
Thus spake Glenn Sullivan
> In my C code which communicates with the Postgres database,
> I have the need to determine the datatype of a column, before
> I have done a SELECT command. I have the name of the column,
> but I cannot seem to figure out how to get the datatype information
> until after I have done a SELECT. Then I can call PQfnumber() and
> PQftype() to get the type. Does anyone know how to do this?
Here's a select statement I use to get the types of a class.
SELECT pg_attribute.attname, pg_type.typname
FROM pg_class, pg_attribute, pg_type
WHERE pg_class.relname = '%s' AND
pg_attribute.attnum > 0 AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.atttypid = pg_type.oid
The "%s" gets replaced by the class name and the typname shows the
name of the type. Here's an example output.
attname |typname
---------+-------
ride_id |int4
name |text
from_date|date
to_date |date
contact |text
phone |text
email |text
url |text
(8 rows)
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-30 02:30 Bob Smither <smither@C-C-I.Com>
parent: Glenn Sullivan <glenn.sullivan@nmr.varian.com>
3 siblings, 1 reply; 11+ messages in thread
From: Bob Smither @ 1998-09-30 02:30 UTC (permalink / raw)
To: Glenn Sullivan <glenn.sullivan@nmr.varian.com>; +Cc: pgsql-sql@hub.org
On Tue, 29 Sep 1998, Glenn Sullivan wrote:
:In my C code which communicates with the Postgres database,
:I have the need to determine the datatype of a column, before
:I have done a SELECT command. I have the name of the column,
:but I cannot seem to figure out how to get the datatype information
:until after I have done a SELECT. Then I can call PQfnumber() and
:PQftype() to get the type. Does anyone know how to do this?
Not at all sure how to do this from C, but from perl I do something like
this:
$temp = `psql -d databasename -c "\\d tablename"`
The item between the `s is executed and the result returned to the
variable. $temp now has the full structure of the table and can be parsed
to extract the column names and types. The double \\ is needed to prevent
perl from thinking that \d is a meta character (I think).
The output of the command between the `s could also be redirected to a
file.
Hope this helps,
======================================================================
Bob Smither, Ph.D. 281-331-2744; fax:-4616 Smither@C-C-I.Com
Windows - making simple things easy, and interesting things impossible
======================================================================
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-30 11:40 James Olin Oden <joden@lee.k12.nc.us>
parent: Bob Smither <smither@C-C-I.Com>
0 siblings, 0 replies; 11+ messages in thread
From: James Olin Oden @ 1998-09-30 11:40 UTC (permalink / raw)
To: Bob Smither <smither@C-C-I.Com>; +Cc: Glenn Sullivan <glenn.sullivan@nmr.varian.com>; pgsql-sql@hub.org
> Not at all sure how to do this from C, but from perl I do something like
> this:
>
> $temp = `psql -d databasename -c "\\d tablename"`
>
> The item between the `s is executed and the result returned to the
> variable. $temp now has the full structure of the table and can be parsed
> to extract the column names and types. The double \\ is needed to prevent
> perl from thinking that \d is a meta character (I think).
>
> The output of the command between the `s could also be redirected to a
> file.
In C he could do essentially the same thing. One approach is to use system as
so:
system("psql -d databasename -c \"\\d tablename\" -o /tmp/somefile");
And then he could open the file /tmp/somefile and parse his way through it.
Also, he can do the what perl does to create pipes (perl is written in C) and
essentially do the same thing as you are doing. I personally would go with
the temp file, using my proccess id to as part of the tempfile name to make it
unique to my process, but it should not be to much work to make the proper
system calls to do the piping...james
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-30 13:19 Bryan White <bryan@arcamax.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bryan White @ 1998-09-30 13:19 UTC (permalink / raw)
To: James Olin Oden <joden@lee.k12.nc.us>; Bob Smither <smither@C-C-I.Com>; +Cc: Glenn Sullivan <glenn.sullivan@nmr.varian.com>; pgsql-sql@hub.org
>> Not at all sure how to do this from C, but from perl I do something like
>> this:
>>
>> $temp = `psql -d databasename -c "\\d tablename"`
>>
>> The item between the `s is executed and the result returned to the
>> variable. $temp now has the full structure of the table and can be
parsed
>> to extract the column names and types. The double \\ is needed to
prevent
>> perl from thinking that \d is a meta character (I think).
>>
>> The output of the command between the `s could also be redirected to a
>> file.
>
>In C he could do essentially the same thing. One approach is to use system
as
>so:
>
> system("psql -d databasename -c \"\\d tablename\" -o /tmp/somefile");
>
>And then he could open the file /tmp/somefile and parse his way through it.
>Also, he can do the what perl does to create pipes (perl is written in C)
and
>essentially do the same thing as you are doing. I personally would go with
>the temp file, using my proccess id to as part of the tempfile name to make
it
>unique to my process, but it should not be to much work to make the proper
>system calls to do the piping...james
Is that not what popen/pclose are for. i.e
FILE *f = popen("psql -d databasename -c \"\\d tablename\"","r");
...
pclose(f);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-09-30 22:21 herouth maoz <herouth@oumail.openu.ac.il>
parent: Glenn Sullivan <glenn.sullivan@nmr.varian.com>
3 siblings, 1 reply; 11+ messages in thread
From: herouth maoz @ 1998-09-30 22:21 UTC (permalink / raw)
To: Glenn Sullivan <glenn.sullivan@nmr.varian.com>; +Cc: pgsql-sql@hub.org
On Tue, 29 Sep 1998, Glenn Sullivan wrote:
> In my C code which communicates with the Postgres database,
> I have the need to determine the datatype of a column, before
> I have done a SELECT command. I have the name of the column,
> but I cannot seem to figure out how to get the datatype information
> until after I have done a SELECT. Then I can call PQfnumber() and
> PQftype() to get the type. Does anyone know how to do this?
In addition to looking up the name of the column, you can use a general
approach, which is also good in case the select is using an EXPRESSION
rather than a column name (e.g. SELECT salary/1000 FROM emp).
The approach is to work on your query, and replace its where clause (or
add one if it doesn't have one) with boolean literal 'false'. This means
that the query will not return any tuples, nor take much toll on the
database, but the parser will parse it and give you back all the necessary
type information.
So, if you want to issue the following query:
SELECT field1, ( field2 * 13 )
FROM table
WHERE field1>1000 AND (( field2 % 4 ) = 3 );
And you want to check the types and lengths first, first issue the query:
SELECT field1, ( field2 * 13 )
FROM table
WHERE 'false'::bool;
You can use PQftype() etc. on the result, and only then issue the real
query, and use the information you gathered in this "dummy" pass.
Herouth
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: [SQL] Getting datatype before SELECT
@ 1998-10-01 16:41 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jackson, DeJuan @ 1998-10-01 16:41 UTC (permalink / raw)
To: herouth maoz <herouth@oumail.openu.ac.il>; Glenn Sullivan <glenn.sullivan@nmr.varian.com>; +Cc: pgsql-sql@hub.org
> On Tue, 29 Sep 1998, Glenn Sullivan wrote:
>
> > In my C code which communicates with the Postgres database,
> > I have the need to determine the datatype of a column, before
> > I have done a SELECT command. I have the name of the column,
> > but I cannot seem to figure out how to get the datatype information
> > until after I have done a SELECT. Then I can call PQfnumber() and
> > PQftype() to get the type. Does anyone know how to do this?
>
> In addition to looking up the name of the column, you can use a
> general
> approach, which is also good in case the select is using an EXPRESSION
> rather than a column name (e.g. SELECT salary/1000 FROM emp).
>
> The approach is to work on your query, and replace its where clause
> (or
> add one if it doesn't have one) with boolean literal 'false'. This
> means
> that the query will not return any tuples, nor take much toll on the
> database, but the parser will parse it and give you back all the
> necessary
> type information.
>
> So, if you want to issue the following query:
>
> SELECT field1, ( field2 * 13 )
> FROM table
> WHERE field1>1000 AND (( field2 % 4 ) = 3 );
>
> And you want to check the types and lengths first, first issue the
> query:
>
> SELECT field1, ( field2 * 13 )
> FROM table
> WHERE 'false'::bool;
>
> You can use PQftype() etc. on the result, and only then issue the real
> query, and use the information you gathered in this "dummy" pass.
>
> Herouth
>
How does this work with joins?
-DEJ
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-10-01 17:39 Glenn Sullivan <glenn.sullivan@nmr.varian.com>
parent: herouth maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 11+ messages in thread
From: Glenn Sullivan @ 1998-10-01 17:39 UTC (permalink / raw)
To: herouth maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql@hub.org
Herouth,
This works great and is indeed faster that other methods
I have now tried.
One question:
The value I get returned from PQftype() for a varchar is 1043
and for an int is 23.
I am uncomfortable just testing for these values. I could
not find in the documentation, what the return values of
PQftype() are. Can anyone point me to that information?
Thanks to all those who have responded, for all the great input
on my original question.
Glenn
herouth maoz wrote:
>
> On Tue, 29 Sep 1998, Glenn Sullivan wrote:
>
> > In my C code which communicates with the Postgres database,
> > I have the need to determine the datatype of a column, before
> > I have done a SELECT command. I have the name of the column,
> > but I cannot seem to figure out how to get the datatype information
> > until after I have done a SELECT. Then I can call PQfnumber() and
> > PQftype() to get the type. Does anyone know how to do this?
>
> In addition to looking up the name of the column, you can use a general
> approach, which is also good in case the select is using an EXPRESSION
> rather than a column name (e.g. SELECT salary/1000 FROM emp).
>
> The approach is to work on your query, and replace its where clause (or
> add one if it doesn't have one) with boolean literal 'false'. This means
> that the query will not return any tuples, nor take much toll on the
> database, but the parser will parse it and give you back all the necessary
> type information.
>
> So, if you want to issue the following query:
>
> SELECT field1, ( field2 * 13 )
> FROM table
> WHERE field1>1000 AND (( field2 % 4 ) = 3 );
>
> And you want to check the types and lengths first, first issue the query:
>
> SELECT field1, ( field2 * 13 )
> FROM table
> WHERE 'false'::bool;
>
> You can use PQftype() etc. on the result, and only then issue the real
> query, and use the information you gathered in this "dummy" pass.
>
> Herouth
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Getting datatype before SELECT
@ 1998-10-02 02:14 D'Arcy J.M. Cain <darcy@druid.net>
parent: Glenn Sullivan <glenn.sullivan@nmr.varian.com>
0 siblings, 0 replies; 11+ messages in thread
From: darcy@druid.net @ 1998-10-02 02:14 UTC (permalink / raw)
To: glenn.sullivan@nmr.varian.com; +Cc: herouth@oumail.openu.ac.il; pgsql-sql@hub.org
Thus spake Glenn Sullivan
> The value I get returned from PQftype() for a varchar is 1043
> and for an int is 23.
> I am uncomfortable just testing for these values. I could
> not find in the documentation, what the return values of
> PQftype() are. Can anyone point me to that information?
Here's a Python script to generate #defines for a C header. I'm sure
you can figure out how to convert it for your needs.
#! /usr/local/bin/python
import string
# change this if you have it somewhere else
for l in open("/usr/local/pgsql/src/include/catalog/pg_type.h").readlines():
tokens = string.split(l)
if len(tokens) == 0 or tokens[0] != "#define": continue
if tokens[1] in ('CASHOID', 'INT2OID', 'INT4OID', 'OIDOID', 'FLOAT4OID', 'FLOAT8OID'):
print l,
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: [SQL] Getting datatype before SELECT
@ 1998-10-06 08:54 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: Herouth Maoz @ 1998-10-06 08:54 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: pgsql-sql
At 18:41 +0200 on 1/10/98, Jackson, DeJuan wrote:
> > The approach is to work on your query, and replace its where clause
> > (or add one if it doesn't have one) with boolean literal 'false'.
> How does this work with joins?
Sorry, I was on vacation...
Shouldn't make any difference. You don't *have* to restrict a join. It
certainly doesn't matter, where the types and lengths of the fields are
important, what the conditions of the join are. Just mention all the table
names. The cartesian product will not be performed, because the condition
is false. Only the types and lengths will be calculated for you.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~1998-10-06 08:54 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-29 17:05 Getting datatype before SELECT Glenn Sullivan <glenn.sullivan@nmr.varian.com>
1998-09-29 21:28 ` David Hartwig <daveh@insightdist.com>
1998-09-30 02:10 ` D'Arcy J.M. Cain <darcy@druid.net>
1998-09-30 02:30 ` Bob Smither <smither@C-C-I.Com>
1998-09-30 11:40 ` James Olin Oden <joden@lee.k12.nc.us>
1998-09-30 22:21 ` herouth maoz <herouth@oumail.openu.ac.il>
1998-10-01 17:39 ` Glenn Sullivan <glenn.sullivan@nmr.varian.com>
1998-10-02 02:14 ` D'Arcy J.M. Cain <darcy@druid.net>
1998-09-30 13:19 Re: [SQL] Getting datatype before SELECT Bryan White <bryan@arcamax.com>
1998-10-01 16:41 RE: [SQL] Getting datatype before SELECT Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-06 08:54 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox