Thread

1. Get Columns from Plan

   Ed Behn <ed@behn.us> — 2018-07-23T01:56:18Z

   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?
          -Ed
   

2. Re: Get Columns from Plan

   Isaac Morland <isaac.morland@gmail.com> — 2018-07-23T02:20:08Z

   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.
   

3. Re: Get Columns from Plan

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-07-23T03:45:18Z

   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