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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
