Thread: Re : overriding default value in inherited column (+ set_value function)

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@

Nico <nicod@tiscalinet.it> writes:
> The function set_value has to be written in C language (plpgsql lang doesn't
> allow parameter passing for trigger functions).

Sure it does --- see TG_NARGS and TG_ARGV[] at
http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-trigger.html

            regards, tom lane

Is there any way to make use of the tableoid either as an argument to the
function or as a reference within the function (in plpgsql)? For example,
I'd like to either

CREATE TRIGGER set_default_value BEFORE INSERT
ON foo FOR EACH ROW EXECUTE PROCEDURE set_value('tableoid');

and within the function set_value():

SELECT p.relname::text FROM pg_class p WHERE p.oid = TG_ARGV[0]::oid );

- or -

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

and within the function set_value():

SELECT p.relname::text FROM pg_class p WHERE p.oid = NEW.tableoid;

The former produces the error

 ERROR:  text_oid: error in "tableoid": can't parse "tableoid"

and the later produces the error:

ERROR:  record new has no field tableoid

I gather the former method is passing the string "tableoid" into the
set_value() function. I just want to be able to write one function that uses
the tableoid value to produce different results instead of unique functions
for each table I create.

-- m@

"Nico" <nicod@tiscalinet.it> wrote in message
news:01032014024502.01280@localhost.localdomain...
> 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@
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

I'd guess you could use TG_RELID or TG_RELNAME inside your trigger.

On Tue, 20 Mar 2001, Matt Magoffin wrote:

> Is there any way to make use of the tableoid either as an argument to the
> function or as a reference within the function (in plpgsql)? For example,
> I'd like to either
>
> CREATE TRIGGER set_default_value BEFORE INSERT
> ON foo FOR EACH ROW EXECUTE PROCEDURE set_value('tableoid');
>
> and within the function set_value():
>
> SELECT p.relname::text FROM pg_class p WHERE p.oid = TG_ARGV[0]::oid );
>
> - or -
>
> CREATE TRIGGER set_default_value BEFORE INSERT
> ON foo FOR EACH ROW EXECUTE PROCEDURE set_value();
>
> and within the function set_value():
>
> SELECT p.relname::text FROM pg_class p WHERE p.oid = NEW.tableoid;
>
> The former produces the error
>
>  ERROR:  text_oid: error in "tableoid": can't parse "tableoid"
>
> and the later produces the error:
>
> ERROR:  record new has no field tableoid
>
> I gather the former method is passing the string "tableoid" into the
> set_value() function. I just want to be able to write one function that uses
> the tableoid value to produce different results instead of unique functions
> for each table I create.

I had to give a loud "Duh!" after reading your response: that's exactly what
I wanted, thanks!

Now I wonder if there is a way for a trigger that's created on a base table
to be fired on any table that inherits from that base table. Otherwise I'm
still stuck creating triggers for each table that I create (that's inherited
from the base table).

For example, if I have:

CREATE TABLE foo (
    "name" text
);

CREATE TRIGGER foo_trigger BEFORE INSERT
ON foo FOR EACH ROW EXECUTE PROCEDURE a_func();

CREATE TABLE bar (
) INHERITS (foo);

I would like foo_trigger to get fired when I execute a

INSERT into bar ("Hello, world.");

but it only seems to fire if the INSERT was on foo, not bar. Any way to do
this?

-- m@

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:Pine.BSF.4.21.0103201248480.30334-100000@megazone23.bigpanda.com...
>
> I'd guess you could use TG_RELID or TG_RELNAME inside your trigger.
>
> On Tue, 20 Mar 2001, Matt Magoffin wrote:
>
> > Is there any way to make use of the tableoid either as an argument to
the
> > function or as a reference within the function (in plpgsql)? For
example,
> > I'd like to either
> >
> > CREATE TRIGGER set_default_value BEFORE INSERT
> > ON foo FOR EACH ROW EXECUTE PROCEDURE set_value('tableoid');
> >
> > and within the function set_value():
> >
> > SELECT p.relname::text FROM pg_class p WHERE p.oid = TG_ARGV[0]::oid );
> >
> > - or -
> >
> > CREATE TRIGGER set_default_value BEFORE INSERT
> > ON foo FOR EACH ROW EXECUTE PROCEDURE set_value();
> >
> > and within the function set_value():
> >
> > SELECT p.relname::text FROM pg_class p WHERE p.oid = NEW.tableoid;
> >
> > The former produces the error
> >
> >  ERROR:  text_oid: error in "tableoid": can't parse "tableoid"
> >
> > and the later produces the error:
> >
> > ERROR:  record new has no field tableoid
> >
> > I gather the former method is passing the string "tableoid" into the
> > set_value() function. I just want to be able to write one function that
uses
> > the tableoid value to produce different results instead of unique
functions
> > for each table I create.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

On Tue, 20 Mar 2001, Matt Magoffin wrote:

> I had to give a loud "Duh!" after reading your response: that's exactly what
> I wanted, thanks!
>
> Now I wonder if there is a way for a trigger that's created on a base table
> to be fired on any table that inherits from that base table. Otherwise I'm
> still stuck creating triggers for each table that I create (that's inherited
> from the base table).
>
> For example, if I have:
>
> CREATE TABLE foo (
>     "name" text
> );
>
> CREATE TRIGGER foo_trigger BEFORE INSERT
> ON foo FOR EACH ROW EXECUTE PROCEDURE a_func();
>
> CREATE TABLE bar (
> ) INHERITS (foo);
>
> I would like foo_trigger to get fired when I execute a
>
> INSERT into bar ("Hello, world.");
>
> but it only seems to fire if the INSERT was on foo, not bar. Any way to do
> this?

Not automatically currently. :(
It's in the general to do, but...