PostgreSQL triggers and passing parameters
- by iandouglas
This is a multi-part question.
I have a table similar to this:
CREATE TABLE sales_data (
Company character(50),
Contract character(50),
top_revenue_sum integer,
top_revenue_sales integer,
last_sale timestamp) ;
I'd like to create a trigger for new inserts into this table, something like this:
CREATE OR REPLACE FUNCTION add_contract()
RETURNS VOID
DECLARE
myCompany character(50),
myContract character(50),
BEGIN
myCompany = TG_ARGV[0];
myContract = TG_ARGV[1];
IF (TG_OP = 'INSERT') THEN
EXECUTE 'CREATE TABLE salesdata_' || $myCompany || '_' || $myContract || ' (
sale_amount integer,
updated TIMESTAMP not null,
some_data varchar(32),
country varchar(2)
) ;'
EXECUTE 'CREATE TRIGGER update_sales_data BEFORE INSERT OR DELETE ON salesdata_' || $myCompany || '_' || $myContract || ' FOR EACH ROW EXECUTE update_sales_data( ' || $myCompany || ',' || $myContract || ', revenue);' ;
END IF;
END;
$add_contract$ LANGUAGE plpgsql;
CREATE TRIGGER add_contract AFTER INSERT ON sales_data FOR EACH ROW EXECUTE add_contract() ;
Basically, every time I insert a new row into sales_data, I want to generate a new table where the name of the table will be defined as something like "salesdata_Company_Contract"
So my first question is how can I pass the Company and Contract data to the trigger so it can be passed to the add_contract() stored procedure?
From my stored procedure, you'll see that I also want to update the original sales_data table whenever new data is inserted into the salesdata_Company_Contract table. This trigger will do something like this:
CREATE OR REPLACE FUNCTION update_sales_data() RETURNS trigger as $update_sales_data$
DECLARE
myCompany character(50) NOT NULL,
myContract character(50) NOT NULL,
myRevenue integer NOT NULL
BEGIN
myCompany = TG_ARGV[0] ;
myContract = TG_ARGV[1] ;
myRevenue = TG_ARGV[2] ;
IF (TG_OP = 'INSERT') THEN
UPDATE sales_data SET
top_revenue_sales = top_revenue_sales + 1,
top_revenue_sum = top_revenue_sum + $myRevenue,
updated = now()
WHERE
Company = $myCompany AND
Contract = $myContract ;
ELSIF (TG_OP = 'DELETE') THEN
UPDATE sales_data SET
top_revenue_sales = top_revenue_sales - 1,
top_revenue_sum = top_revenue_sum - $myRevenue,
updated = now()
WHERE
Company = $myCompany AND
Contract = $myContract ;
END IF;
END;
$update_sales_data$ LANGUAGE plpgsql;
This will, of course, require that I pass several parameters around within these stored procedures and triggers, and I'm not sure (a) if this is even possible, or (b) practical, or (c) best practice and we should just put this logic into our other software instead of asking the database to do this work for us.
To keep our table sizes down, as we'll have hundreds of thousands of transactions per day, we've decided to partition our data using the Company and Contract strings as part of the table names themselves so they're all very small in size; file IO for us is faster and we felt we'd get better performance.
Thanks for any thoughts or direction.
My thinking, now that I've written all of this out, is that maybe we need to write stored procedures where we pass our insert data as parameters, and call that from our other software, and have the stored procedure do the insert into "sales_data" then create the other table. Then, have a second stored procedure to insert new data into the salesdata_Company_Contract tables, where the table name is passed to the stored proc as a parameter, and again have that stored proc do the insert, then update the main sales_data table afterward.
What approach would you take?
