postgresql: using NEW.* in dynamic command for EXECUTE
i try to create a plpgsql trigger for postgresql 8.3 which automatically partitions a table on before insert by the id column
if the destination table doesnt exist it will be created, and the insert goes there
so i created the insert statement with the new table name like this
exec_insert := 'INSERT INTO '||TG_TABLE_SCHEMA||'.'||TG_RELNAME||'_'||destinationid||' VALUES('||NEW.*||')';
EXECUTE exec_insert;
resulting in the error:
ERROR: NEW used in query that is not in a rule
i have 2 questions:
- is it even possible to use NEW in EXECUTE or is t开发者_运维问答here some mistake in the statement?
- if its just not possible, anyone knows how to get the values out of NEW so i can use them in the statement? the only thing which comes to my mind is using information_schema to retrieve column names for the main table and then try to access NEW's values dynamically - which i also dont know how :(
thx
You could use
...
_sql varchar(100)= 'insert into some_table values ($1.*)';
...
execute _sql using new;
...
I found this hidden in some remote areas :D . I am using 9.1
This setup works fine over here (version 8.4 and 9.0):
CREATE TABLE customer
(
id bigserial NOT NULL,
name text,
datecreated timestamp with time zone DEFAULT now(),
CONSTRAINT customer_pkey PRIMARY KEY (id) USING INDEX TABLESPACE pg_default
);
CREATE OR REPLACE FUNCTION test_trigger()
RETURNS trigger AS
$BODY$
declare
query text;
begin
query := 'INSERT INTO customer_' || substring(NEW.name,1,1) || ' VALUES(' || NEW.id || ','''|| NEW.name||''')';
EXECUTE query;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
query := 'CREATE TABLE "customer_' || substring(NEW.name,1,1) ||
'"( CONSTRAINT "customer_' || substring(NEW.name,1,1) ||'_name_check" CHECK (lower(substring(name, 1, 1)) = ''' || substring(NEW.name,1,1) || '''::text)
) INHERITS (customer); CREATE INDEX "i_customer_' || substring(NEW.name,1,1) ||'_name" ON customer_' || substring(NEW.name,1,1) ||' USING btree(name);';
EXECUTE query;
query := 'INSERT INTO customer_' || substring(NEW.name,1,1) || ' VALUES(' || NEW.id || ','''|| NEW.name||''')';
EXECUTE query;
query := 'ANALYZE "customer_' || substring(NEW.name,1,1) || '"';
EXECUTE query;
RETURN NULL;
end;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;
CREATE TRIGGER t_customer
BEFORE INSERT OR UPDATE OR DELETE
ON customer
FOR EACH ROW
EXECUTE PROCEDURE test_trigger();
CREATE TABLE customer
(
id bigserial NOT NULL,
name text,
datecreated timestamp with time zone DEFAULT now(),
CONSTRAINT customer_pkey PRIMARY KEY (id) USING INDEX TABLESPACE pg_default
);
CREATE OR REPLACE FUNCTION test_trigger()
RETURNS trigger AS
$BODY$
declare
query text;
begin
query := 'INSERT INTO customer_' || substring(NEW.name,1,1) || ' VALUES(' || NEW.id || ','''|| NEW.name||''')';
EXECUTE query;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
query := 'CREATE TABLE "customer_' || substring(NEW.name,1,1) ||
'"( CONSTRAINT "customer_' || substring(NEW.name,1,1) ||'_name_check" CHECK (lower(substring(name, 1, 1)) = ''' || substring(NEW.name,1,1) || '''::text)
) INHERITS (customer); CREATE INDEX "i_customer_' || substring(NEW.name,1,1) ||'_name" ON customer_' || substring(NEW.name,1,1) ||' USING btree(name);';
EXECUTE query;
query := 'INSERT INTO customer_' || substring(NEW.name,1,1) || ' VALUES(' || NEW.id || ','''|| NEW.name||''')';
EXECUTE query;
query := 'ANALYZE "customer_' || substring(NEW.name,1,1) || '"';
EXECUTE query;
RETURN NULL;
end;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;
CREATE TRIGGER t_customer
BEFORE INSERT OR UPDATE OR DELETE
ON customer
FOR EACH ROW
EXECUTE PROCEDURE test_trigger();
INSERT INTO customer(name) VALUES ('john'), ('peter');
精彩评论