howto create dynamic table name in plpgsql function.
От | Thies C. Arntzen |
---|---|
Тема | howto create dynamic table name in plpgsql function. |
Дата | |
Msg-id | F81D0BB7-4E40-4F3E-B979-A2BC70421E38@thieso.net обсуждение исходный текст |
Ответы |
Re: howto create dynamic table name in plpgsql function.
|
Список | pgsql-general |
hello, i have a table with documents that have an id and belong to a pool: (the sample tables have no indices, i know) Table "public.asset" Column | Type | Modifiers ----------+---------+----------- asset_id | integer | pool_id | integer | content | text | each pool belongs to a customer: Table "public.pool" Column | Type | Modifiers -------------+---------+----------- pool_id | integer | customer_id | integer | now, for speed reasons i want to create one tsearch2 index per customer. Table "public.ftx_1" Column | Type | Modifiers ----------------+----------+----------- asset_id | integer | content_vector | tsvector | i have one ftx_<customer_id> tabe per customer so i wrote a trigger: drop function update_ftx() cascade; CREATE FUNCTION update_ftx() RETURNS TRIGGER AS ' DECLARE cid integer; BEGIN SELECT INTO cid customer_id FROM pool WHERE pool_id = NEW.pool_id; IF NOT FOUND THEN RAISE EXCEPTION ''Invalid pool.''; END IF; insert into ftx values (NEW.asset_id, to_tsvector(NEW.content)); -- this it where my knowledge ends;-) -- i want to say something like -- insert into ftx_||cid values (NEW.asset_id, to_tsvector (NEW.content)); -- to insert into ftx_<customer_id> RETURN new; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER update_ftx BEFORE UPDATE OR INSERT ON asset FOR EACH ROW EXECUTE PROCEDURE update_ftx(); but i can't get the insert to work as i don't know the syntax.. any help would be greatly appreciated! ..tc
В списке pgsql-general по дате отправления: