How to describe columns (get their names, data types, etc.) of a SQL query in PostgreSQL

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

Solution

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