0

I need a way to get a "description" of the columns from a SELECT query (cursor), such as their names, data types, precision, scale, etc., in PostgreSQL (or better yet PL/pgSQL).

I'm transitioning from Oracle PL/SQL, where I can get such description using a built-in procedure dbms_sql.describe_columns. It returns an array of records, one for each column of a given (parsed) cursor.

EDB has it implemented too (https://www.enterprisedb.com/docs/en/9.0/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-127.htm#P13324_681237)

An examples of such query:

select col1 from tab where col2 = :a

I need an API (or a workaround) that could be called like this (hopefully):

select query_column_description('select col1 from tab where col2 = :a');

that will return something similar to:

{{"col1","numeric"}}

Why? We build views where these queries become individual columns. For example, view's query would look like the following:

select (select col1 from tab where col2 = t.colA) as col1::numeric
  from tab_main t
Ivan C
  • 123
  • 2
  • 5
  • Something similar is available using psycopg2 (https://stackoverflow.com/questions/10252247/how-do-i-get-a-list-of-column-names-from-a-psycopg2-cursor), but I was hoping I could stay within PL/pgSQL, so I don't have to deal with (install/learn/etc.) psycopg2 just for this (and maybe some other cool/needed features I've yet to discover) – Ivan C Oct 19 '17 at 18:55
  • There is nothing built-in on the server side. On the client side I know that JDBC can do that, and I guess ODBC as well. I assume both interfaces use [SPI](https://www.postgresql.org/docs/current/static/spi-interface-support.html) for this. –  Oct 19 '17 at 19:08
  • Please upvote the answer if it is ok for you. Thanks. – Indent Oct 19 '17 at 21:31

1 Answers1

0

http://sqlfiddle.com/#!17/21c7a/2

You can use systems table :

First step create a temporary view with your query (without clause where)

create or replace view temporary view a_view as
    select col1 from tab

then select 

select
    row_to_json(t.*)
from (
    select 
        column_name,
        data_type
    from
        information_schema.columns
    where
        table_schema = 'public' and
        table_name   = 'a_view'
) as t
Indent
  • 4,675
  • 1
  • 19
  • 35