sql: pg_typeof builtin should include type information from column definition

[rafiss]

Postgres:

rafiss@127:postgres> create table typs(a int2, b int4, c int8, d char, e "char", f text);
CREATE TABLE

rafiss@127:postgres>  insert into typs values(1,2,3,'a','b','c');
INSERT 0 1

rafiss@127:postgres>  select pg_typeof(a), pg_typeof(b), pg_typeof(c), pg_typeof(d), pg_typeof(e), pg_typeof(f) from typs;
+-------------+-------------+-------------+-------------+-------------+-------------+
| pg_typeof   | pg_typeof   | pg_typeof   | pg_typeof   | pg_typeof   | pg_typeof   |
|-------------+-------------+-------------+-------------+-------------+-------------|
| smallint    | integer     | bigint      | character   | "char"      | text        |
+-------------+-------------+-------------+-------------+-------------+-------------+
SELECT 1

But in CockroachDB, the function reports the type of the datum used internally in the SQL execution engine. The type information is only added later when sending data over the wire.

root@:26257/defaultdb> create table typs(a int2, b int4, c int8, d char, e "char", f string);
CREATE TABLE

root@:26257/defaultdb> insert into typs values(1,2,3,'a','b','c');
INSERT 1

root@:26257/defaultdb> select pg_typeof(a), pg_typeof(b), pg_typeof(c), pg_typeof(d), pg_typeof(e), pg_typeof(f) from typs;
  pg_typeof | pg_typeof | pg_typeof | pg_typeof | pg_typeof | pg_typeof
------------+-----------+-----------+-----------+-----------+------------
  bigint    | bigint    | bigint    | text      | text      | text
(1 row)

The execution engine doesn't have access to the real type information. The pg_typeof builtin operates on Datums, which only can be one of a few "canonical types."

Epic CRDB-2474
Jira issue: CRDB-3415

[rafiss]

relates to #48613

[dvarrazzo]

Probably related to this: pg_typeof() returns text on crdb, whereas on Pg it returns regtype.

As a consequence, an expression such as pg_typeof(X) = 'text'::regtype works on pg but fails on crdb. Adding a ::regtype to the LHS makes the expression portable across the DBs.