Analyzing the types of prepared statements

Hello List,

My colleagues and I have some interest in creating an automated
PG->Haskell bridge, that will read the PG catalog for a
particular namespace or namespaces and generate a Haskell module
with functions of appropriate types, creating functions for each
stored procedure and simple tuple types for each table and view.

It would also be nice to scan all the source code for queries
and, ECPG-like, generate code for each query and replace the
query with a reference to it. As long as we can ask Postgres to
inspect a SQL statement for its argument types and return type
(if it has one), it's straightforward to generate the code and
replace the inline SQL statements (which are marked with
quasi-quotations, and thus distinguishable syntactically from
plain strings).

However, it is not clear to me at this juncture how to get the
return type for a statement, given its text. Preparing and
looking it up in pg_prepared_statements will retrieve the
argument types but not the return type. Wrapping the query in a
SQL stored procedure which returns record doesn't cause the
server to infer and store a more specific type.

It is possible to find the return type of a select by replacing
the parameters with NULLs, wrapping the statement in CREATE TEMP
VIEW and examining the type of the view. For statements
involving INSERT RETURNING or DELETE RETURNING, though, this
technique is not viable (no data modifying statements allowed in
WITH clauses for views).

To be able to examine inserts and deletes too, one could drop
all constraints on all tables and then create a temp table that
selects from a CTE with the to-be-analyzed statement in it,
where all '?' have been replaced by NULL. But I'm sure I'm not
the first person who's had this problem and I wonder if there's
a better way. I'd prefer to be able to run the analysis step on
a DB without changing it.

--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B