From: | "Thies C(dot) Arntzen" <thies(at)thieso(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | "Thies C(dot) Arntzen" <thies(at)thieso(dot)net> |
Subject: | howto create dynamic table name in plpgsql function. |
Date: | 2005-11-25 20:23:33 |
Message-ID: | F81D0BB7-4E40-4F3E-B979-A2BC70421E38@thieso.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Casanova | 2005-11-25 20:37:10 | Re: howto create dynamic table name in plpgsql function. |
Previous Message | CSN | 2005-11-25 20:19:01 | Can this pl/pgsql be simplified? |