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 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
"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 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!