Обсуждение: 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