5

In Microsoft Sql Server, there is a proc sys.sp_describe_first_result_set which takes an arbitrary sql query string and returns data describing the types that would be in the result set, were it to be executed. Note, this is describing the results of a complex query, not a table. MSDN reference

Is there anything similar for Postgres?

Improve this question

3 Answers 3

Reset to default
2

I tried to do this by creating a stored procedure to create temperory view then check information_schema.columns before droping the view.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That's actually quite genius!
0

There is at the protocol level - in the form of the Describe message in the extended query protocol.

I'm not aware of any SQL-level equivalent for arbitrary SQL strings. Good idea though; raise it on pgsql-general . I suspect it'd be pretty easy to implement.

In fact, a quick look at exec_describe_statement_message in src/backend/tcop/postgres.c shows that the brains are in SendRowDescriptionMessage in src/backend/access/common/printtup.c. It shouldn't be hard to write a simple C extension to do similar work and produce a resultset. Hopefully a couple of hours work if you're familar with the codebase; the idea would be:

  • Feed the SQL string into the parser/rewriter/planner to obtain a plan
  • Find the targetlist of the top level node, if any. This will be the RETURNING node for DML, or the top-level Query tlist for a SELECT.
  • Feed the tlist into ExecTypeFromTL
  • Loop over the tupledesc entries, skipping resjunk columns by looking them up in the tlist, and emit a row for each non-resjunk output from the query.
Improve this answer

7 Comments

There might be a (semantic) problem with domains and UDTs. Though the type can be reported, the frontend might not be able to make eany sense of this output.
@joop Good thought. There's not much that can be done about that, except teaching the client to query the catalogs to learn about the types/domains. After all it's no different to built-in non-SQL-standard types like json. For domains, we can also optionally resolve them to their underlying types, but that might be misleading in other ways.
@joop In the end, if the client is using those types, they're expected to know what they mean. If they're accepting arbitrary queries they'll have to know how to query the information_schema or system catalogs.
@CraigRinger: that's where the semantics kick in. For instance: GIS-extentions will always have a textual represntation, but the client will need to parse and process this. So in effect, the DESCRIBE is only useful in trivial cases. Finally: knowing that a column (expression) in the query result has type integer NOT NULL and name q1_barbie_score_with_voodoo_correction will stil not help the frontend.
I know. It was just to illustrate that the knowledge of the technical type is insufficient. The client needs to know the semantics. (this is what I use to call the XML trap ;-)
|
0

I wrote a little extension for PostgreSQL that returns the basic information about columns of a result set.

After installing it, execute SELECT * FROM describe_resultset('<your query>'), and you'll get something like this:

 column_name | column_type | column_type_name | column_len | column_attrs
-------------+-------------+------------------+------------+--------------
 foo         |          23 | int4             |          4 |
 bar         |        1700 | numeric          |         -1 |
(2 rows)

It uses SPI_prepare() to parse the statement and obtain the necessary information.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.