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

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

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

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?

-- 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

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...