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" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> 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" <[EMAIL PROTECTED]>
> > 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
