Waiting for 9.1 – ALTER TYPE

On 26th of September, Peter Eisentraut committed patch which adds:

ADD ALTER TYPE ... ADD/DROP/ALTER/RENAME ATTRIBUTE
 
LIKE WITH TABLES, this also requires allowing the existence OF
composite types WITH zero attributes.
 
reviewed BY KaiGai Kohei

Custom types, do not get much coverage, but I personally like them. I much prefer to use them for SRF functions (than multiple OUT parameters).

The problem was that changing these types was never easy. you had to drop the type, and create it again.

Now, it's no longer the case – you can “easily" modify them. Why “easily" and not easily? Well, changing this datatype still required function recompilation, so it's not solving all problems. But it's a step in right direction. And besides – if you are changing definition of recordset returned from function – you're likely changing the function itself anyway.

So, let's see how it works:

$ CREATE TYPE test AS (
    x int4,
    y int4
);
 
$ \d test
Composite TYPE "public.test"
 COLUMN |  TYPE
--------+---------
 x      | INTEGER
 y      | INTEGER

Now, we can:

$ ALTER TYPE test ALTER ATTRIBUTE x TYPE int8;
ALTER TYPE
 
$ ALTER TYPE test DROP ATTRIBUTE y;
ALTER TYPE
 
$ ALTER TYPE test ADD ATTRIBUTE z INT8;
ALTER TYPE
 
$ ALTER TYPE test RENAME ATTRIBUTE x TO new_x;
ALTER TYPE
 
\d test
Composite TYPE "public.test"
 COLUMN |  TYPE
--------+--------
 new_x  | BIGINT
 z      | BIGINT

So, how does it influence the functions?

Let's test.

$ CREATE FUNCTION testit() RETURNS setof test AS $$
DECLARE
    reply test;
BEGIN
    reply.new_x := 123;
    reply.z := 456;
    RETURN NEXT reply;
    RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
 
$ SELECT * FROM testit();
 new_x |  z
-------+-----
   123 | 456
(1 ROW)

So far so good. So, let's change the type definition, in a way that (at least theoretically) shouldn't matter for the function:

$ ALTER TYPE test ADD COLUMN new_column int4;
ALTER TYPE
 
$ SELECT * FROM testit();
ERROR:  wrong record TYPE supplied IN RETURN NEXT
CONTEXT:  PL/pgSQL FUNCTION "testit" line 7 at RETURN NEXT

Oops. But – this happens only because this particular db connection already used the function. If I disconnect, and reconnect again (without any more changes):

$ SELECT * FROM testit();
 new_x |  z  | new_column 
-------+-----+------------
   123 | 456 |     [NULL]
(1 ROW)

Alternatively, I could simply reload the same function body with CREATE OR REPLACE:

$ ALTER TYPE test ADD attribute new_column2 int4;
ALTER TYPE
 
$ SELECT * FROM testit();
ERROR:  wrong record TYPE supplied IN RETURN NEXT
CONTEXT:  PL/pgSQL FUNCTION "testit" line 7 at RETURN NEXT
 
$ SELECT * FROM testit();
ERROR:  wrong record TYPE supplied IN RETURN NEXT
CONTEXT:  PL/pgSQL FUNCTION "testit" line 7 at RETURN NEXT
 
$ CREATE OR REPLACE FUNCTION testit() RETURNS setof test AS $$ 
DECLARE 
    reply test; 
BEGIN 
    reply.new_x := 123; 
    reply.z := 456; 
    RETURN NEXT reply; 
    RETURN; 
END; 
$$ LANGUAGE plpgsql;
CREATE FUNCTION
 
$ SELECT * FROM testit();
 new_x |  z  | new_column | new_column2 
-------+-----+------------+-------------
   123 | 456 |     [NULL] |      [NULL]
(1 ROW)

Sweet. I think that if it altering datatype would actually invalidate all previously compiled plans/functions – it would be even better, but as I wrote earlier – when you're changing datatype you're likely to be changing function body as well, so it's not a big deal.

2 thoughts on “Waiting for 9.1 – ALTER TYPE”

  1. It might be also worth mentioning that if any table is using it as a column type, the type cannot be modified.

  2. Custom types, do not get much coverage, but I personally like them. I much prefer to use them for SRF functions (than multiple OUT parameters).

    Note that you can also use

    RETURNS TABLE

    . I used this a bunch in PGXN::Manager. See for example 

    by_tag_json

    . I like how this keeps the implicit custom type associated with the function without requiring

    OUT

    parameters.

    —Theory

Comments are closed.