Thread: generate query string in trigger func
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
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
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
>
Hi, I need to download postgreSQL develope package for ver. 8.3.8, but I can not find the ...i386.rpm anywhere. Please, could someone guide me to the right place? Kind regards, Paul
On Mon, Nov 15, 2010 at 2:53 PM, Malm Paul <paul.malm@saabgroup.com> wrote: >I need to download postgreSQL develope package for ver. 8.3.8 Why 8.3.8? 8.3.10 is the same, only with issues fixed. http://www.postgresql.org/ftp/binary/v8.3.10/linux/rpms/ hth WBL -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Mon, 2010-11-15 at 13:53 +0000, Malm Paul wrote: > I need to download postgreSQL develope package for ver. 8.3.8, but I > can not find the ...i386.rpm anywhere. > > Please, could someone guide me to the right place? Which distro? If RHEL 5, then: http://yum.pgsqlrpms.org/8.3/redhat/rhel-5-i386/postgresql-8.3.8-1PGDG.rhel5.i386.rpm Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz