Hi,
I'm experimenting with partitioning.
I have split up my original table into 15 parts and i have written a trigger that will handle the INSERTs.
I didn't want to write the same insert statement 15 times, so i thought it would be a good thing to just dynamically build the insert statement in the trigger function, concatenating the partition name to the sql.
But all these syntaxes didn't work.
Is that some restriction of trigger functions?
I don't know yet if it would really be faster, i'd want to test that (it probably is slower because of the extra string processing).
Must i use an IF statement for each table in the partitioned table? (why?)
Cheers,
WBL
CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES (NEW.*)';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Below goes wrong because grdcellocid and locid can be NULL
The query string will be NULL if i concat a null value to it, so i need to use coalesce on potential null attributes
I've tried several syntaxes: 'NULL', 'NULL::integer', '\N', '\\N', it's all bad:
ERROR: invalid input syntax for integer: "NULL::integer"
But i am not quoting these values inside the query string, it seems like there is some quote_literal() active in trigger functions?
CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO grid_cells_'||NEW.grdid||' VALUES ('||NEW.grdcelid||','||NEW.grdid||','||NEW.x||','||NEW.y||','||NEW.taxid||','||NEW.yearstart||','||NEW.yearstop||','||NEW.count_rejected||','||NEW.count_submitted||','||NEW.count_ind_validated||','||NEW.count_pop_validated||','||coalesce(NEW.locid, '\\N')||','||coalesce(NEW.grdcellocid, '\\N')||','||NEW.count_exotic||','||NEW.created||' );';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw