Обсуждение: Determine data type of columns

I have a general purpose utilty for entering data into web based forms.  The
function that parses the data and inserts the record into a table is largely
unaware of the structure of the table it is operating on.  I am currenly
having problems with integer fields.  If I blindly quote all fields the back
end will complain about casting on integer fields.  Also I would like to do
some primitive field validation based on type before submitting to the back
end.

This brings me to my question.  The PQftype function returns an OID but I
don't see any documentation on how to interpret it.  Are the values constant
for the built in types (specifically int4, text, and date)?
Do I have to execute another query to determine the actual type?
Is there a way to encode the values in an insert/update statement such that
they will be quietly converted to the proper type on the backend?

I am using 6.3.2 but will be upgrading to 6.4 shortly.  Will that make this
any easier?

Bryan White
ArcaMax Inc.
Yorktown VA
www.arcamax.com

Thus spake Bryan White
> This brings me to my question.  The PQftype function returns an OID but I
> don't see any documentation on how to interpret it.  Are the values constant
> for the built in types (specifically int4, text, and date)?
> Do I have to execute another query to determine the actual type?
> Is there a way to encode the values in an insert/update statement such that
> they will be quietly converted to the proper type on the backend?

Look at how I do it in PyGreSQL.  The code is available at
    http://www.druid.net/pygresql/

First, I run this Python script to get the proper OIDs.

#! /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,

This gets me my #defines.  My assumption is that if it isn't one of those
then I just treat it as text.  I include this in my C source with a
comment about how naughty I am.  Then I use these values in the functions
that have to do the conversions.

--
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.

"Bryan White" <bryan@arcamax.com> writes:
> This brings me to my question.  The PQftype function returns an OID but I
> don't see any documentation on how to interpret it.  Are the values constant
> for the built in types (specifically int4, text, and date)?

The OID for a type is the OID of the row for the type in the system
table pg_type.  Try
    select oid,* from pg_type;
and much will become clear.  You can go from type name to OID and
vice versa with appropriate queries on pg_type.

OIDs for the system-defined types should be the same across all
installations.  OIDs for user-defined types are likely to change
if you initdb and reload the database.

> Is there a way to encode the values in an insert/update statement such that
> they will be quietly converted to the proper type on the backend?

You should be able to do something like
        '42'::int4
for any standard type --- they all have string-to-datatype converters.
The parser essentially does the above for you when it sees an unadorned
42.  It will also supply a free conversion from string literal to
most of the more complicated types (datetime, for instance).  I imagine
that as an error-detection measure it will not supply a free
string-to-int or string-to-float conversion --- you have to write the
cast if you want a string literal to be read as an int or float.

            regards, tom lane

At 23:21 +0200 on 10/11/98, Bryan White wrote:


> I have a general purpose utilty for entering data into web based forms.  The
> function that parses the data and inserts the record into a table is largely
> unaware of the structure of the table it is operating on.  I am currenly
> having problems with integer fields.  If I blindly quote all fields the back
> end will complain about casting on integer fields.  Also I would like to do
> some primitive field validation based on type before submitting to the back
> end.

It may be worth noticing that if you use COPY rather than INSERT, you don't
have to quote neither numbers nor other data types. You do have to quote
delimiter characters (tab, newline, backslash - or whatever you chose as
your copy delimiters) with a backslash. That can be easily done even with
the most "blind" front end, which knows nothing about the fields.

I know, I know. You want to do updates and some type-based data checking as
well. But this may be the answer in cases you don't, and be faster at that.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma