Return to Question

I have multiple tables that have the exact same column names, they only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a Trigger that calls a Trigger Function named "trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trfn_tbl_log_a
  OWNER TO postgres;

So all my trigger functions are exactly the same, they only differ in the table where they select the columns (in its first IF STATEMENT):

select timeidx FROM tbl_log_a WHERE fnname...
select timeidx FROM tbl_log_b WHERE fnname...
select timeidx FROM tbl_log_c WHERE fnname...
...

My Trigger definition script is...

CREATE TRIGGER trfn_tbl_log_a
  BEFORE INSERT
  ON tbl_log_a
  FOR EACH ROW
  EXECUTE PROCEDURE trfn_tbl_log_a();

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT timeidx FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT timeidx FROM tbl_log_%letter%

Is it possible in Postgres 9.1? Please notice that my Trigger Function, calls several variables from the table...

timeidx
fnname
timetype
stmtserial
NEW.timedix
and many more that I didn't add for size. but all in the example list all the kinds.

So I don't know if selecting one columns from the current table and store it as a variable could help me, since the Trigger Function works on multiple columns inside that table and also use default postgresql internal Variable "NEW.", also if Im already using a default postgresql internal Variable "NEW." I don't know if I can add TG_TABLE_NAME and TG_TABLE_SCHEMA to that.

I guess I would like to store the table name itself as a variable. Is that possible???

Thanks Advanced.

I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables from a to z. Each table has a trigger that calls a trigger function which does the exact same thing:

SELECT columnname FROM tbl_log_a

Other than that, all my trigger functions do the exact same thing. They differ in that they:

select columnname FROM tbl_log_a
select columnname FROM tbl_log_b
select columnname FROM tbl_log_c
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? I'm reading about dynamically determined tables. Any clue? I would like to store the table name itself inside a variable, not the columns inside that table, because the trigger function works on multiple columns inside that table.

TG_TABLE_NAME
TG_TABLE_SCHEMA

I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a Trigger that calls a Trigger Function named "trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trfn_tbl_log_a
  OWNER TO postgres;

So all my trigger functions are exactly the same, they only differ in the table where they select the columns (in its first IF STATEMENT):

select timeidx FROM tbl_log_a WHERE fnname...
select timeidx FROM tbl_log_b WHERE fnname...
select timeidx FROM tbl_log_c WHERE fnname...
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? Please notice that my Trigger Function, calls several variables from the table...

timeidx
fnname
timetype
stmtserial
NEW.timedix
and many more that I didn't add for size. but all in the example list all the kinds.

So I don't know if selecting one columns from the current table and store it as a variable could help me, since the Trigger Function works on multiple columns inside that table and also use default postgresql internal Variable "NEW.", also if Im already using a default postgresql internal Variable "NEW." I don't know if I can add TG_TABLE_NAME and TG_TABLE_SCHEMA to that.

I guess I would like to store the table name itself as a variable. Is that possible???

Thanks Advanced.

I have multiple tables that have the exact same column names, they only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a Trigger that calls a Trigger Function named "trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trfn_tbl_log_a
  OWNER TO postgres;

So all my trigger functions are exactly the same, they only differ in the table where they select the columns (in its first IF STATEMENT):

select timeidx FROM tbl_log_a WHERE fnname...
select timeidx FROM tbl_log_b WHERE fnname...
select timeidx FROM tbl_log_c WHERE fnname...
...

My Trigger definition script is...

CREATE TRIGGER trfn_tbl_log_a
  BEFORE INSERT
  ON tbl_log_a
  FOR EACH ROW
  EXECUTE PROCEDURE trfn_tbl_log_a();

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT timeidx FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT timeidx FROM tbl_log_%letter%

Is it possible in Postgres 9.1? Please notice that my Trigger Function, calls several variables from the table...

timeidx
fnname
timetype
stmtserial
NEW.timedix
and many more that I didn't add for size. but all in the example list all the kinds.

So I don't know if selecting one columns from the current table and store it as a variable could help me, since the Trigger Function works on multiple columns inside that table and also use default postgresql internal Variable "NEW.", also if Im already using a default postgresql internal Variable "NEW." I don't know if I can add TG_TABLE_NAME and TG_TABLE_SCHEMA to that.

I guess I would like to store the table name itself as a variable. Is that possible???

Thanks Advanced.

I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a Trigger that calls a Trigger Function named "trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trfn_tbl_log_a
  OWNER TO postgres;

So all my trigger functions are exactly the same, they only differ in that they (in its first IF STATEMENT):

select columnname FROM tbl_log_a
select columnname FROM tbl_log_b
select columnname FROM tbl_log_c
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? Please notice that my Trigger Function, calls several variables from the table...

timeidx
fnname
timetype
stmtserial
NEW.timedix
and many more that I didn't add for size. but all in the example list all the kinds.

So I don't know select one columns from the current table and store it as a variable could help me, since the Trigger Function works on multiple columns inside that table. also Im already using a default postgresql internal Variable "NEW." so I don't know if I can add TG_TABLE_NAME and TG_TABLE_SCHEMA to that.

I guess I would like to store the table name itself inside a variable. Is that possible???

Thanks Advanced.

I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a Trigger that calls a Trigger Function named "trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trfn_tbl_log_a
  OWNER TO postgres;

So all my trigger functions are exactly the same, they only differ in the table where they select the columns (in its first IF STATEMENT):

select timeidx FROM tbl_log_a WHERE fnname...
select timeidx FROM tbl_log_b WHERE fnname...
select timeidx FROM tbl_log_c WHERE fnname...
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? Please notice that my Trigger Function, calls several variables from the table...

timeidx
fnname
timetype
stmtserial
NEW.timedix
and many more that I didn't add for size. but all in the example list all the kinds.

So I don't know if selecting one columns from the current table and store it as a variable could help me, since the Trigger Function works on multiple columns inside that table and also use default postgresql internal Variable "NEW.", also if Im already using a default postgresql internal Variable "NEW." I don't know if I can add TG_TABLE_NAME and TG_TABLE_SCHEMA to that.

I guess I would like to store the table name itself as a variable. Is that possible???

Thanks Advanced.