Thread: Information about columns

Hi,

Is there some way to find out meta-information about the columns generated
by any SELECT query?  If the SELECT returns values from a regular table or
a view, I can use pg_class and pg_attribute to get the info I need:

CREATE TABLE foobar (quant int);
SELECT quant FROM foobar;
SELECT attname, attnum FROM pg_attribute, pg_class WHERE attrelid=pg_class.oid AND relname='foobar';

But what if the columns belong to a "virtual" table instead, as per the
(silly) example below?

SELECT 1, 2*quant FROM foobar;

Thanks in advance for any help you might give me!
Best regards,
Dario Teixeira

On Mon, 2009-06-22 at 05:26 -0700, Dario Teixeira wrote:

> Is there some way to find out meta-information about the columns generated
> by any SELECT query?

How are you talking to the database ? ODBC? JDBC? LibPQ? Something else?
Or do you want this from within PL/PgSQL ?

You'll usually find that this information is most easily obtained via
your client driver. I don't know if it's exposed at the SQL level, but
it's certainly available from the underlying PostgreSQL network
protocol.

--
Craig Ringer

2009/6/22 Dario Teixeira <darioteixeira@yahoo.com>:
>
> Hi,
>
> Is there some way to find out meta-information about the columns generated
> by any SELECT query?  If the SELECT returns values from a regular table or
> a view, I can use pg_class and pg_attribute to get the info I need:
>
> CREATE TABLE foobar (quant int);
> SELECT quant FROM foobar;
> SELECT attname, attnum FROM pg_attribute, pg_class WHERE attrelid=pg_class.oid AND relname='foobar';
>
> But what if the columns belong to a "virtual" table instead, as per the
> (silly) example below?
>
> SELECT 1, 2*quant FROM foobar;
>


you can do it on low level - look on functions

PQdescribePrepared
PQdescribePortal

regards
Pavel Stehule

> Thanks in advance for any help you might give me!
> Best regards,
> Dario Teixeira
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi,

> How are you talking to the database ? ODBC? JDBC? LibPQ? Something else?
> Or do you want this from within PL/PgSQL ?

I'm hacking on a client-side library which talks directly to the Postmaster
using the wire protocol [1]. I need this information to improve some of the
nullability-detection heuristics used by the library.

Cheers,
Dario

[1] http://pgocaml.berlios.de/

On Jun 22, 2009, at 11:43 AM, Dario Teixeira wrote:

>> How are you talking to the database ? ODBC? JDBC? LibPQ? Something
>> else?
>> Or do you want this from within PL/PgSQL ?
>
> I'm hacking on a client-side library which talks directly to the
> Postmaster
> using the wire protocol [1]. I need this information to improve some
> of the
> nullability-detection heuristics used by the library.

The information you want is always returned from the query as a row
description message. This includes the type oid of real and computed
columns.

See the RowDescription message on this page for details:

http://www.postgresql.org/docs/8.3/static/protocol-message-formats.html



John DeSoi, Ph.D.

Hi,

> The information you want is always returned from the query
> as a row description message. This includes the type oid of
> real and computed columns.
>
> See the RowDescription message on this page for details:

Thanks for the reply.  Note that is in fact RowDescription that PG'OCaml
is already using to obtain the type oid of real and computed columns.
The problem is that in some circumstances RowDescription does not provide
a pg_class OID where I (naïvely perhaps) expect it.

To be more precise, when issuing a SELECT for tables and views, the
associated pg_class OID is always provided.  Doing a SELECT on 'foobar'
and 'foobar1' will work:

CREATE TABLE foobar (quant int);

CREATE VIEW foobar1 AS SELECT * FROM foobar;

*However*, if I create a new type (which has an associated pg_class entry),
and define a function which returns a SETOF that type, RowDescription will
not tell me its OID.  For example:

CREATE TYPE foobar_t AS (quant int);

CREATE FUNCTION foobar2 () RETURNS SETOF foobar_t AS
'SELECT * FROM foobar' LANGUAGE sql STABLE;

Is this a bug or a conscious decision?  And on the latter case, how can
I retrieve the pg_class OID of foobar_t?

Thanks again,
Dario Teixeira

On Jun 22, 2009, at 4:57 PM, Dario Teixeira wrote:

> *However*, if I create a new type (which has an associated pg_class
> entry),
> and define a function which returns a SETOF that type,
> RowDescription will
> not tell me its OID.  For example:
>
> CREATE TYPE foobar_t AS (quant int);
>
> CREATE FUNCTION foobar2 () RETURNS SETOF foobar_t AS
> 'SELECT * FROM foobar' LANGUAGE sql STABLE;
>
> Is this a bug or a conscious decision?  And on the latter case, how
> can
> I retrieve the pg_class OID of foobar_t?

I don't think it is a bug because the documentation clearly states "if
the field can be identified as a column of a specific table, the
object ID of the table; otherwise zero." A type is not the same as a
table.

It is not as elegant as you would like, but maybe one idea is to
create your own alias of the built in type so you can determine the
answer just by looking at the column type. For example, instead of
using "int" in CREATE TYPE above, create a your own type equivalent to
an integer.



John DeSoi, Ph.D.

Dario Teixeira <darioteixeira@yahoo.com> writes:
> *However*, if I create a new type (which has an associated pg_class entry),
> and define a function which returns a SETOF that type, RowDescription will
> not tell me its OID.  For example:
> ...
> Is this a bug or a conscious decision?

It's intentional; IIRC, the current behavior is defined that way because
that's what the JDBC driver needs to implement the JDBC specs.  Putting
information about composite types where information about tables is
expected would confuse the heck out of existing client code.

            regards, tom lane

Hi,

> It is not as elegant as you would like, but maybe one idea
> is to create your own alias of the built in type so you can
> determine the answer just by looking at the column type. For
> example, instead of using "int" in CREATE TYPE above, create
> a your own type equivalent to an integer.

Thanks for the suggestion.  Yes, that should work, but it really
is a very cumbersome and brittle solution.

Cheers,
Dario

Hi,

> It's intentional; IIRC, the current behavior is defined that way because
> that's what the JDBC driver needs to implement the JDBC specs.  Putting
> information about composite types where information about tables is
> expected would confuse the heck out of existing client code.

Thanks for the info.  So, given that RowDescription is a dead-end, is there
any other way I can determine the composite type associated with a function
return?  If, for example, I have the following defined in the database,

CREATE TABLE foobar (quant int);
CREATE TYPE foobar_t AS (quant int);
CREATE FUNCTION foobar2 () RETURNS SETOF foobar_t AS
'SELECT * FROM foobar' LANGUAGE sql STABLE;

and the client side issues a "SELECT * FROM foobar2 ()", is there some
means of determining that a function "foobar2" returning "foobar_t" was
invoked?  I don't care how convoluted the steps may be, since all of this
happens at compile-time and has a zero runtime penalty.

Barring client-side parsing of SQL (a dumb idea), the only solution I can
think of requires the client code to explicitly mark the function invoked.
I would rather avoid this solution, however, since it is error prone and
defeats the premise of the library.

Thanks again for your time and attention!
Cheers,
Dario

darioteixeira@yahoo.com writes:
> Thanks for the info.� So, given that RowDescription is a dead-end, is there
> any other way I can determine the composite type associated with a function
> return?

Why do you think that's a useful activity for client-side code to engage
in?

            regards, tom lane

Hi,

> Why do you think that's a useful activity for client-side
> code to engage in?

Strongly typed languages like Ocaml and Haskell deal with the possibility
of missing values by having "option types".  Though at first glance SQL's
NULL seems like a similar concept, in fact NULL is more like a constraint
on a table column instead of defining a different type.  This impedance
mismatch causes some trouble when writing bindings that aim to preserve
type-safety.

I doubt there is a clean way around this (barring Postgresql implementing
option types).  Therefore, I'm working on a workaround that involves the
Postgresql side annotating the nullability of type definitions by issuing
comments on the type (using COMMENT ON).  Yes, it is a hack, but will solve
my problem as long as I can determine the return type (and thus fetch its
comment) associated with a query.

I know other people faced this problem before when writing Ocaml or Haskell
bindings, and that is why I know there is no straightforward solution.  Still,
I welcome any thinking-outside-the-box suggestions...

Cheers,
Dario Teixeira

Dario Teixeira <darioteixeira@yahoo.com> writes:
> I doubt there is a clean way around this (barring Postgresql implementing
> option types).  Therefore, I'm working on a workaround that involves the
> Postgresql side annotating the nullability of type definitions by issuing
> comments on the type (using COMMENT ON).  Yes, it is a hack, but will solve
> my problem as long as I can determine the return type (and thus fetch its
> comment) associated with a query.

Okay, but that has nothing to do with either functions or composite
types.

The reasonable way to handle this in Postgres (IMHO) would be to define
not-null domains over the types you care about, and then lobby us to
have RowDescription return domain OIDs instead of smashing domains to
their base types.  I don't recall the exact reasoning for the domain
smashing behavior; perhaps it could be made adjustable.

            regards, tom lane