Thread: Getting datatype before SELECT
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
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;
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.
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 ======================================================================
> 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
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
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
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.