Get types of Postgres query result without executing query

Question

Given a running Postgres cluster, how do I get an array of type names for the columns of an arbitrary query result? I'd prefer to avoid executing the query since the queries might be prepared meaning I can't run the queries without specifying concrete values for the prepared expressions.

Example schema

CREATE TYPE book_type AS ENUM ('FICTION', 'NONFICTION');

CREATE TABLE books (
  book_id   SERIAL PRIMARY KEY,
  book_type book_type NOT NULL DEFAULT 'FICTION'
);

How do I get the type names of the columns for the returned rows for the following query?

SELECT book_id,
       book_type,
       'arbitrary_column',
       '2021-01-14'::date - INTERVAL '3 hour'
FROM books
WHERE book_id = $1;

The expected type names for the above query are:

['int', 'varchar', 'varchar', 'timestamp'] 

Is this possible via SQL? If not, is there an extension or C function? I know the types are returned when executing a query, but I'd like to know if it's possible to get the types without executing the query.

Related

https://dba.stackexchange.com/questions/75015/query-to-output-names-and-data-types-of-a-query-table-or-view - Provides an answer by creating a temp table using the query and then introspecting the table. Clever and it works, but requires executing the query.

Answer 1

This is possible for programming languages with a reasonable implementation of Prepare in the Postgres driver.

When you run prepare, the Postgres wire protocol returns the OIDs of the result columns, similar to the parse step of the extend query protocol.

In the Go pgx library, the Prepare method returns the parameter OIDS as part of the StatementDescription type.

In Java, JDBC's PreparedStatement.getMetaData().getColumnType() looks like it returns the OID.

Once we have the OIDs, we can use the pg_type Postgres catalog table to find the rest of the type information.

For example, to find the details of an array type:

SELECT
  arr_typ.oid           AS oid,
  -- typename: Data type name.
  arr_typ.typname::text AS type_name,
  elem_typ.oid          AS elem_oid,
  -- typtype: b for a base type, c for a composite type (e.g., a table's
  -- row type), d for a domain, e for an enum type, p for a pseudo-type,
  -- or r for a range type.
  arr_typ.typtype       AS type_kind
FROM pg_type arr_typ
  JOIN pg_type elem_typ ON arr_typ.typelem = elem_typ.oid
WHERE arr_typ.typisdefined
  AND arr_typ.typtype = 'b' -- Array types are base types
  -- If typelem is not 0 then it identifies another row in pg_type. 
  AND arr_typ.typelem > 0
  -- For a fixed-size type, typlen is the number of bytes in the internal
  -- representation of the type. But for a variable-length type, typlen is
  -- negative. -1 indicates a "varlena" type (one that has a length word), -2
  -- indicates a null-terminated C string.
  AND arr_typ.typlen = -1
  AND arr_typ.oid = ANY ('{1}'::oid[]);