Hello
2010/11/15 Willy-Bas Loos <willybas@gmail.com>:
> 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;
>
>
you can try
CREATE OR REPLACE FUNCTION grid_cells_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO ' || quote_ident('grid_cells_' || new.grid) ||
' VALUES($1.*)' USING new;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
it working on my 9.0
>
> 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?
>
use a quote_nullable function instead - or better USING clause
Regards
Pavel Stehule
>
>
> 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
>
