Re : overriding default value in inherited column (+ set_value function) - Mailing list pgsql-general

Actually not possible with the "DEFAULT" sintax inside the CREATE TABLE.
Instead, you could use explicit triggers, for example:

CREATE table foo (
 "type"    int2
);

CREATE table bar (
 "type"    int2
) INHERITS (foo);

CREATE TRIGGER set_default_value BEFORE INSERT
    ON foo FOR EACH ROW
    EXECUTE PROCEDURE set_value("type", 0);

CREATE TRIGGER set_default_value BEFORE INSERT
    ON bar FOR EACH ROW
    EXECUTE PROCEDURE set_value("type", 1);

The function set_value has to be written in C language (plpgsql lang doesn't
allow parameter passing for trigger functions).

Has someone already written that function?

regards, nico


> From: "Matt Magoffin" <mmagoffin@proxicom.com>
> X-Newsgroups: comp.databases.postgresql.general
> Subject: overriding default value in inherited column
> Date: Mon, 19 Mar 2001 18:39:27 -0800
>
> Is there an easy way to override the defined default value of a column in
> an inherited table? For example:
>
> CREATE table foo (
>  "type"    int2 DEFAULT 0
> );
>
> CREATE table bar (
>  "type"    int2 DEFAULT 1
> ) INHERITS (foo);
>
> This gives the error:
>
> ERROR: CREATE TABLE: attribute "type" already exists in inherited schema
>
> which is understandable. In essence what I want to do is have each table
> schema default to a different value.
>
> -- m@