map_to_pg_oid

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › PostgreSQL compatibility. Returns the PostgreSQL type OID (object identifier) for a type name. It's a helper of the pg_catalog schema, and it knows few names: to find the type of a value or a column, use typeof() or DESCRIBE. pg_catalog.map_to_pg_oid is the same function.

Syntax

map_to_pg_oid(type_name)
pg_catalog.map_to_pg_oid(type_name)

Returns: INTEGER.

Aliases: pg_catalog.map_to_pg_oid.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_maps_map_to_pg_oid. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of five type names. The last row has no name:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_maps_map_to_pg_oid"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.type_names"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, 'bool'), (2, 'BOOLEAN'), (3, 'int4'), (4, 'VARCHAR'), (5, NULL)")

[]
[]
[(5,)]

What it does

map_to_pg_oid returns the OID that the PostgreSQL catalog gives a type name, or NULL if it doesn't know the name:

• 'bool' returns 16. The other names tried return NULL: 'BOOLEAN', 'int4', 'INTEGER', 'BIGINT', 'VARCHAR', 'text', 'DATE', 'TIMESTAMP', 'DOUBLE' and 'MAP'.
• Names are compared exactly. The plan in How it runs in VAST shows the function as a CASE expression that compares the name with a fixed list of lower-case names, such as 'bool', 'int', 'bigint' and 'date', so 'BIGINT' and 'DATE' don't match.
• It doesn't work on maps. The name comes from the PostgreSQL catalog, not from the MAP type.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT map_to_pg_oid(NULL), map_to_pg_oid('bool'), pg_catalog.map_to_pg_oid('bool'), typeof(map_to_pg_oid('bool'))")
run("SELECT map_to_pg_oid('BOOLEAN'), map_to_pg_oid('int4'), map_to_pg_oid('text'), map_to_pg_oid('nope'), "
    "map_to_pg_oid('')")
run("SELECT map_to_pg_oid(123)")

[(None, 16, 16, 'INTEGER')]
[(None, None, None, None, None)]
InternalError: Conversion Error: Could not convert string 'bool' to INT32

Examples

The OID of each name in the table:

run(f"SELECT id, name, map_to_pg_oid(name) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'bool', 16), (2, 'BOOLEAN', None), (3, 'int4', None), (4, 'VARCHAR', None), (5, None, None)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). The scan reads the name column, and the Query Engine computes map_to_pg_oid() for each row it returns. The plan shows the scan and the step above it, which holds the whole lookup:

plan(f"SELECT map_to_pg_oid(name) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│ CASE  WHEN ((name = 'bool'│
│ )) THEN (16) WHEN ((name =│
│  'int16')) THEN (21) WHEN │
│ ((name = 'int')) THEN (23)│
│  WHEN ((name = 'bigint')) │
│  THEN (20) WHEN ((name =  │
│ 'date')) THEN (1082) WHEN │
│  ((name = 'time')) THEN   │
│   (1083) WHEN ((name =    │
│  'datetime')) THEN (1114) │
│    WHEN ((name = 'dec'))  │
│  THEN (1700) WHEN ((name =│
│  'float')) THEN (700) WHEN│
│  ((name = 'double')) THEN │
│    (701) WHEN ((name =    │
│   'bpchar')) THEN (1043)  │
│  WHEN ((name = 'binary')) │
│  THEN (17) WHEN ((name =  │
│  'interval')) THEN (1186) │
│       WHEN ((name =       │
│ 'timestamptz')) THEN (1184│
│ ) WHEN ((name = 'timetz'))│
│  THEN (1266) WHEN ((name =│
│  'bit')) THEN (1560) WHEN │
│  ((name = 'guid')) THEN   │
│    (2950) ELSE NULL END   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: name     │
│                           │
└───────────────────────────┘

Make it faster

• Use typeof() or DESCRIBE to find a type. typeof(value) returns the type name of any value in the query, and DESCRIBE lists the type of every column of a table, without a lookup that returns NULL for most names. See Similar functions.
• Look a name up once. If you need an OID, look it up in a query of its own (SELECT map_to_pg_oid('bool')) rather than for every row of a table.

Limitations and errors

• Few names are known. The plan lists the names it compares with, and the match is exact. Don't build on it to map types; use typeof() or DESCRIBE.
• The argument must be a string. A number fails with Could not convert string 'bool' to INT32, and a list with Type VARCHAR with value 'bool' can't be cast to the destination type LIST: the errors name 'bool', not your value.

run("SELECT map_to_pg_oid(['bool'])")

InternalError: Conversion Error: Type VARCHAR with value 'bool' can't be cast to the destination type LIST

Similar functions

run("SELECT map_to_pg_oid('bool'), typeof(true), typeof(1), typeof('a')")
run(f"DESCRIBE {TABLE}")

[(16, 'BOOLEAN', 'INTEGER', 'VARCHAR')]
[('id', 'BIGINT', 'YES', None, None, None), ('name', 'VARCHAR', 'YES', None, None, None)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• List, struct and map functions: the map functions (map_to_pg_oid isn't one of them).
• Data types in SQL: the type names the VAST Query Engine uses.
• DESCRIBE: the columns and types of a table.