Обсуждение: Getting column names/types from select query?

Hi all,

I am interested in figuring out how to get the names and types of the columns from an arbitrary query. Essentially, I
wantto be able to take a query like: 

CREATE TABLE foo(
    bar bigserial,
    baz varchar(256)
);

SELECT * FROM foo WHERE bar = 42;

and figure out programmatically that the select will return a column "bar" of type bigserial, and a column "foo" of
typevarchar(256). I would like this to work for more complex queries as well (joins, CTEs, etc). 

I've found https://wiki.postgresql.org/wiki/Query_Parsing, which talks about related ways to hook into postgres, but
thatseems to only talk about the parse tree — a lot more detail and processing seems to be required in order to figure
outthe output types. It seems like there should be somewhere I can hook into in postgres that will get me this
information,but I have no familiarity with the codebase, so I don't know the best way to get this. 

How would you recommend that I approach this? I'm comfortable patching postgres if needed, although if there's a
solutionthat doesn't require that, I'd prefer that. 

Thanks,

:w

"Wesley Aptekar-Cassels" <me@wesleyac.com> writes:
> I am interested in figuring out how to get the names and types of the
> columns from an arbitrary query.

Where do you need this information?  Usually the easiest way is to
prepare (plan) the query and then extract metadata, for instance
PQprepare and PQdescribePrepared if using libpq.

            regards, tom lane

> Where do you need this information?

I'm writing some code that takes a given query, and generates type-safe bindings for it, so people can write SQL
queriesand get structs (or vectors of structs) out the other end. So I'm pretty flexible about where I get it, given
thatit'll be part of my build/codegen process. I hadn't seen libpq yet, I'll look into that — thanks!