Thread: Issue With PQftable()

Hello List:

I have a situation where I'm doing a select statement of the form:
SELECT column_1, column_2, my_func(column_3) AS "column_3",
column_4... FROM my_table INNER JOIN other_table ON ...

There are several tables involved in the query. Note that "my_func(column_3)"
from above does some formatting of the data. I issue the command with
PQsendQuery(). When loading a widget with data, I have a need of knowing the
table oid from which the data came. I have been using PQftable() to get that
information, but when I added the "my_func(column_3)" to the query, the value
of PQftable() for each of the columns referenced by calls to my_func() comes
back as zero. PQfname() works correctly because I added the AS "column_3" to
the query.

So, is this the expected behavior or is this a bug? If this is the expected
behavior, is there anything I can do to get hold of the table oid as part of
the select statement?

Using the C api with PostgreSQL version:
rnd=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)

TIA

Terry Lee Tucker wrote:
>
> There are several tables involved in the query. Note that "my_func(column_3)"
> from above does some formatting of the data. I issue the command with
> PQsendQuery(). When loading a widget with data, I have a need of knowing the
> table oid from which the data came. I have been using PQftable() to get that
> information, but when I added the "my_func(column_3)" to the query, the value
> of PQftable() for each of the columns referenced by calls to my_func() comes
> back as zero.

But my_func(column) can do anything with your data. What if I write:

CREATE FUNCTION my_func(int4) RETURNS int4 AS 'SELECT 2' LANGUAGE SQL;

Here it doesn't matter what data I pass into the function I always get
"2" back, so how can you say the data comes from a table?

Now, since there clearly isn't a way to answer your original question,
maybe you need a different question. What do you use the table oid for?
--
   Richard Huxton
   Archonet Ltd

Richard,

Thanks for the response. I see your point. I've been using the table oid to
find a node in a linked list of structures that define several attributes of
a column in relation to the interface. Matching on the column name doesn't
work because some columns share the same name across tables and this function
is a generic one working on all the interfaces; however, I can give the
column a prefix with the AS portion of the SELECT statement that would give
me a clue as to what to do next. I think I have a workaround.

Thanks for the help...

On Monday 12 December 2005 07:20 am, Richard Huxton saith:
> Terry Lee Tucker wrote:
> > There are several tables involved in the query. Note that
> > "my_func(column_3)" from above does some formatting of the data. I issue
> > the command with PQsendQuery(). When loading a widget with data, I have a
> > need of knowing the table oid from which the data came. I have been using
> > PQftable() to get that information, but when I added the
> > "my_func(column_3)" to the query, the value of PQftable() for each of the
> > columns referenced by calls to my_func() comes back as zero.
>
> But my_func(column) can do anything with your data. What if I write:
>
> CREATE FUNCTION my_func(int4) RETURNS int4 AS 'SELECT 2' LANGUAGE SQL;
>
> Here it doesn't matter what data I pass into the function I always get
> "2" back, so how can you say the data comes from a table?
>
> Now, since there clearly isn't a way to answer your original question,
> maybe you need a different question. What do you use the table oid for?
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq