Thread: RE: [SQL] Getting datatype before SELECT

RE: [SQL] Getting datatype before SELECT

From
"Jackson, DeJuan"
Date:
> 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

RE: [SQL] Getting datatype before SELECT

From
Herouth Maoz
Date:
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