Skip to content

Working with Types

Every function and package covered in the Compatibility chapters is a genuine, faithful conversion. One category of object is the sole, structural exception: real orafce's custom Oracle-compatible data types. This chapter explains why, in plain terms, before the next few chapters get into what orafce_no_c offers instead.

What real orafce's types actually are

oracle.varchar2(n) and oracle.nvarchar2(n) are not domains or simple wrappers — they're genuine custom PostgreSQL base types, each with its own compiled I/O functions (to convert the type to and from text and binary form) and typmod support, which is what lets you write a length right into the type name, like VARCHAR2(100).

That combination is only possible with C, for two independent reasons, both confirmed directly against PostgreSQL rather than assumed:

  1. A custom base type's I/O functions must accept a cstring parameter. Neither PL/pgSQL nor SQL-language functions can declare one — PostgreSQL rejects it outright, with a plain "PL/pgSQL functions cannot accept type cstring" / "SQL functions cannot have arguments of type cstring" error.
  2. A DOMAIN — the one type-like object PL/pgSQL/SQL can create — cannot accept a typmod at all. CREATE TABLE t (x my_domain(10)) fails immediately with type modifier is not allowed for type "my_domain", in both a table-column context and a PL/pgSQL variable declaration.

Put together: there is no way, in pure PL/pgSQL or SQL, to build anything that behaves like VARCHAR2(n) — a single object that accepts an arbitrary length parameter the way a real type does.

The workaround, in one sentence

Since one parameterized type is impossible, orafce_no_c instead ships a large family of fixed-length domains — one per length — so that VARCHAR2(100) in your migrated code becomes oracle.varchar_100b instead: a real, ordinary, installable PostgreSQL object, just not VARCHAR2(100) itself.

This is a deliberate, documented workaround, not a type conversion in the same sense as every other function in this project. It's optional (a separate, manual install — see Installing the VARCHAR2/NVARCHAR2 Types), it has one real behavioral gap you need to know about (explicit casts always error rather than truncating — see Why These Types Error Instead of Truncating), and you can tune exactly how many domains get generated (see Customizing the Type Family).

Continue to VARCHAR2 and NVARCHAR2 for the full naming scheme and how to migrate existing code to it.