Get Columns from Plan

On 22 July 2018 at 21:56, Ed Behn <ed@behn.us> wrote:

I'm tinkering with the idea of creating a Procedural Language plugin for
Haskell. As such I'm reading up on the SPI and prepared statements. The
idea is that a statement will be prepared at compile time and executed at
run-time. Therefore, I want to be able to determine the columns (names and
types) that are returned by a plan without executing it. It seems like this
should be a straight-forward task, but there doesn't seem to be a mechanism
to do this.

Is there a way to get the columns for a plan at compile time? If not, why?

It looks to me like PQdescribePrepared() gives you most of what you want:

https://www.postgresql.org/docs/current/static/libpq-exec.html

You can get the types of the columns. However, it's not immediately obvious
to me how to get the column names. For query results there is PQfname() to
get the column names, but I believe that requires running the query. I
suppose you could add "LIMIT 0" to the end of the query and run it, but
that doesn't feel ideal.

Ed Behn <ed@behn.us> writes:

I'm tinkering with the idea of creating a Procedural Language plugin for
Haskell. As such I'm reading up on the SPI and prepared statements. The
idea is that a statement will be prepared at compile time and executed at
run-time. Therefore, I want to be able to determine the columns (names and
types) that are returned by a plan without executing it. It seems like this
should be a straight-forward task, but there doesn't seem to be a mechanism
to do this.

Sure: SPI_plan_get_plan_sources(), iterate through that list of
CachedPlanSources, inspect the resultDesc tupledescs. The reasons this
isn't terribly well documented include:

* You'll need to decide what your semantics are for cases where there's
more or less than one list entry with a result.

* Consider the possibility that the result tupdesc changes from time to
time, eg the result of "SELECT * FROM foo" can mutate due to ALTER TABLE.

regards, tom lane