Re: [SQL] Getting datatype before SELECT

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