PostgreSQL triggers and passing parameters

Question

This is a multi-part question.

I have a table similar to:

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?

Answer 1

It looks like you just need to reference the NEW or OLD parameters entering your table. Examples can readily be found in the excellent Postgresql documentation here. Reference your values as NEW.Company and NEW.Contract.