Borrow Database Column Type for a PSQL Variable

Adriano dos Santos Fernandes

Tracker reference CORE-1356.

This feature extends the implementation in v.2 whereby domains became available as “data types” for declaring variables in PSQL. Now it is possible to borrow the data type of a column definition from a table or view for this purpose.

Syntax Pattern

    data_type ::=
         <builtin_data_type>
       | <domain_name>
       | TYPE OF <domain_name>
       | TYPE OF COLUMN <table or view>.<column>

Note

TYPE OF COLUMN gets only the type of the column. Any constraints or default values defined for the column are ignored.

Examples

CREATE TABLE PERSON (
  ID INTEGER,
  NAME VARCHAR(40)
  );

CREATE PROCEDURE SP_INS_PERSON (
  ID TYPE OF COLUMN PERSON.ID,
  NAME TYPE OF COLUMN PERSON.NAME
  )
  AS
DECLARE VARIABLE NEW_ID TYPE OF COLUMN PERSON.ID;
BEGIN
  INSERT INTO PERSON (ID, NAME)
    VALUES (:ID, :NAME)
    RETURNING ID INTO :NEW_ID;
END

Hidden Trap!

In v.2.5 and beyond, it is possible to alter the data type of a column, even if the column is referenced in a stored procedure or trigger, without an exception being thrown. Because compiled PSQL is stored statically as a binary representation (“BLR”) in a BLOB, the original BLR survives even a backup and restore. Being static, the BLR is not updated by the data type change, either.

This means that, for variables declared using the TYPE OF syntax, as well as the affected columns from the tables, together with any view columns derived from them, the compiled BLR is broken by the change of data type. At best, the BLR will be flagged as “needing attention” but tests show that the flag is not set under all conditions.

In short, the engine now no longer stops you from changing the type of a field that has any dependencies in compiled PSQL. It will be a matter for your own change control to identify the affected procedures and triggers and recompile them to accommodate the changes.