CODEWITHSUNDEEP
postgresqltriggersplpgsqldynamic-sqlpostgresql-9.2
octobot
octobot

Reputation: 89

Dynamically create table after INSERT to another table

I have a table:

create table monthly_tran (id int,somedata text);

A row will add to this table only once in every month of a year. After inserting a row to this table, I need to create a new table:

create table monthly_proc_X(id int,tranid int,somedata text);

X in monthly_proc_X should be the id of the newly inserted row in table monthly_tran.
For example If (9,'some values') is inserted, a table monthly_proc_9 should get created.

Note : PostgreSQL 9.2

Upvotes: 1

Views: 350

Answers (1)

Vivek S.
Vivek S.

Reputation: 21895

You can create a trigger like this

CREATE OR REPLACE FUNCTION dynTable() RETURNS TRIGGER AS $$
    BEGIN
execute 'create table monthly_proc_'||new.id||'(id int,tranid int,somedata text)';
return new;
    END;
$$ LANGUAGE plpgsql;

and assign to monthly_tran

CREATE TRIGGER tgr_create_table AFTER INSERT ON monthly_tran
FOR EACH ROW EXECUTE PROCEDURE dynTable();

The trigger dynTable() will create table dynamically when a new row is inserted to monthly_tran

Upvotes: 1

Related Questions