Search Postgresql Archives

generate query string in trigger func

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux