All pastes #22026 Raw Edit

Mine

public text v1 · immutable
#22026 ·published 2005-09-03 05:27 UTC
rendered paste body
CREATE TABLE rate (
    id SERIAL UNIQUE NOT NULL,
    description TEXT,
    pattern TEXT NOT NULL,
    prefixlen INTEGER NOT NULL DEFAULT 0,
    primary key(id)
    );

CREATE FUNCTION update_prefixlen() RETURNS opaque AS '
    DECLARE
    BEGIN
        UPDATE rate SET prefixlen = length(pattern) WHERE id = NEW.id;
        RETURN NEW;
    END;
    ' LANGUAGE plpgsql;

CREATE TRIGGER set_prefixlen
    AFTER INSERT OR UPDATE
    ON rate FOR EACH STATEMENT
    EXECUTE PROCEDURE update_prefixlen();

test=> INSERT INTO rate (description, pattern) VALUES ('test 1', 'abc');
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "update_prefixlen" line 3 at SQL statement