Getting column names/types from select query?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Getting column names/types from select query?

Wesley Aptekar-Cassels
Hi all,

I am interested in figuring out how to get the names and types of the columns from an arbitrary query. Essentially, I want to 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 type varchar(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 that seems to only talk about the parse tree — a lot more detail and processing seems to be required in order to figure out the 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 solution that doesn't require that, I'd prefer that.

Thanks,

:w


Reply | Threaded
Open this post in threaded view
|

Re: Getting column names/types from select query?

Tom Lane-2
"Wesley Aptekar-Cassels" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Getting column names/types from select query?

Wesley Aptekar-Cassels
> 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 queries and get structs (or vectors of structs) out the other end. So I'm pretty flexible about where I get it, given that it'll be part of my build/codegen process. I hadn't seen libpq yet, I'll look into that — thanks!