Search Postgresql Archives

Re: howto create dynamic table name in plpgsql function.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/25/05, Thies C. Arntzen <thies@xxxxxxxxxx> wrote:
> 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>
>

execute 'insert into ftx_' || ' cid || ' values (' || NEW.asset_id || ', '
                                                             || '
to_tsvector (' || NEW.content || ' ));';

>     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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
>       message can get through to the mailing list cleanly
>


--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux