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
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
Show quoted text
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
Import Notes
Reply to msg id not found: AANLkTi=TS5tarpMu1rbZJRqAe35wGB6ppXCrDzrzTRHp@mail.gmail.com
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