Thread: Dealing with tsvector in fuctions for data split

Dealing with tsvector in fuctions for data split

From

Francisco Reyes

Date:

06 August 2007, 02:13:15

I am trying to create a function to handle inserts in a database with
partitions in a particular table.

The function looks like:
AS $$
DECLARE
    v_sql TEXT;
BEGIN
    v_sql := 'INSERT INTO messagecatalog_'||
to_char(NEW.timestampfield,'YYYY')||
    '(field1, field2) values ('
  ||New.field1||','||New.field2||')
 ')';
    EXECUTE v_sql;
    RETURN NULL;
END
$$;

The problem I am running into is that one of the fields is a tsvector and
when I try to concatenate it wit the rest of the string it gives an error.
The only thing I can think of so far is to start a transaction, insert
without the tsvector and then do an update to get the tsvector in.

Anyone else had to deal with tsvectors in a fuction?

I am copying some existing data so all the rows already have the tsvector.

If nothing else works I may just not deal with the tsvector in the function
and see if I just re-create the tsvector for each record as I am insert the
data.

Re: Dealing with tsvector in fuctions for data split

From

Oleg Bartunov

Date:

06 August 2007, 04:38:30

Francisco,

you need to say us standard information about pg version, error message you
got. Also, it'd be worth to show simplified version of your function, which
demonstrates your problem.

Oleg
On Mon, 6 Aug 2007, Francisco Reyes wrote:

> I am trying to create a function to handle inserts in a database with
> partitions in a particular table.
>
> The function looks like:
> AS $$
> DECLARE
>   v_sql TEXT;
> BEGIN
>   v_sql := 'INSERT INTO messagecatalog_'||
> to_char(NEW.timestampfield,'YYYY')||
>   '(field1, field2) values ('
> ||New.field1||','||New.field2||')
> ')';
>   EXECUTE v_sql;
>   RETURN NULL;
> END
> $$;
>
> The problem I am running into is that one of the fields is a tsvector and
> when I try to concatenate it wit the rest of the string it gives an error.
> The only thing I can think of so far is to start a transaction, insert
> without the tsvector and then do an update to get the tsvector in.
>
> Anyone else had to deal with tsvectors in a fuction?
>
> I am copying some existing data so all the rows already have the tsvector.
>
> If nothing else works I may just not deal with the tsvector in the function
> and see if I just re-create the tsvector for each record as I am insert the
> data.
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>              http://www.postgresql.org/docs/faq
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Re: Dealing with tsvector in fuctions for data split

From

Tom Lane

Date:

06 August 2007, 11:42:25

Francisco Reyes <lists@stringsutils.com> writes:

>     v_sql := 'INSERT INTO messagecatalog_'||
> to_char(NEW.timestampfield,'YYYY')||
>     '(field1, field2) values ('
>   ||New.field1||','||New.field2||')
>  ')';
>     EXECUTE v_sql;

This is not a particularly good way of accomplishing partitioning,
as you'll be needing *every* part of your application to be explicitly
aware of the exact partitioning scheme.

However, if you insist on doing it like that, don't you need
quote_literal() for the field values?

            regards, tom lane

Re: Dealing with tsvector in fuctions for data split

From

Francisco Reyes

Date:

06 August 2007, 14:22:59

Tom Lane writes:

> This is not a particularly good way of accomplishing partitioning,
> as you'll be needing *every* part of your application to be explicitly
> aware of the exact partitioning scheme.

I am trying to follow the presentation at PGcon2007 by Robert Treat.
I created a master table, then the children which inherit from the master.
The children have checks to make sure the checks are mutually exclusive.

The function is for the insert trigger.
Applications will insert against the master table table and the function is
to be called by an insert trigger in the master table. The function is to
redirect each insert to the proper child table. Users will access the data
through the master table. I will have constraint_exclusion = on so only the
appropriate tables get accessed on selects.

> However, if you insist on doing it like that, don't you need
> quote_literal() for the field values?

ERROR:  function quote_literal(tsvector) does not exist

Re: Dealing with tsvector in fuctions for data split

From

Francisco Reyes

Date:

06 August 2007, 14:27:34

Oleg Bartunov writes:

> Francisco,
>
> you need to say us standard information about pg version

Postgresql 8.2 running in FreeBSD.

> error message you got.

ERROR:  operator does not exist: text || tsvector

> Also, it'd be worth to show simplified version of your function, which
> demonstrates your problem.

I did include that.

>> The function looks like:
>> AS $$
>> DECLARE
>>   v_sql TEXT;
>> BEGIN
>>   v_sql := 'INSERT INTO messagecatalog_'||
>> to_char(NEW.timestampfield,'YYYY')||
>>   '(field1, field2) values ('
>> ||New.field1||','||New.field2||')
>> ')';
>>   EXECUTE v_sql;
>>   RETURN NULL;
>> END
>> $$;

In the code above field1 is text and field2 is tsvector.

Basically I am trying to do partitioning. I have a master table, some
children which inherit from the master. Inserts will be redirected with a
trigger from the master to the children. The function I am having the
problem with is what the trigger calls to do the redirection.

Re: Dealing with tsvector in fuctions for data split

From

Francisco Reyes

Date:

08 August 2007, 12:18:29

Francisco Reyes writes:

> ERROR:  operator does not exist: text || tsvector
>
>> Also, it'd be worth to show simplified version of your function, which
>> demonstrates your problem.
>
>
> I did include that.
>
>>> The function looks like:
>>> AS $$
>>> DECLARE
>>>   v_sql TEXT;
>>> BEGIN
>>>   v_sql := 'INSERT INTO messagecatalog_'||
>>> to_char(NEW.timestampfield,'YYYY')||
>>>   '(field1, field2) values ('
>>> ||New.field1||','||New.field2||')
>>> ')';
>>>   EXECUTE v_sql;
>>>   RETURN NULL;
>>> END
>>> $$;
>
> In the code above field1 is text and field2 is tsvector.


Any suggestions?
Anyone else has dealt with tsvectors in a partition environment?
If sow how did you get the split function/rule to insert into the child
table?

Re: Dealing with tsvector in fuctions for data split

From

Oleg Bartunov

Date:

08 August 2007, 15:28:17

On Wed, 8 Aug 2007, Francisco Reyes wrote:

> Francisco Reyes writes:
>
>> ERROR:  operator does not exist: text || tsvector

what'd you expect from this operation ? In 8.2 you can cast tsvector
to text like this:

=# select textin( tsvector_out( strip( to_tsvector('1 b c'))))::text || 'some text'::text;
        ?column?
----------------------
  '1' 'b' 'c'some text

You should think about removing positional information from tsvector
using strip() function.


In CVS HEAD standard CAST should works.

postgres=# select cast( 'a b c'::tsvector AS text);
text
-------------
'a' 'b' 'c'


>>
>>> Also, it'd be worth to show simplified version of your function, which
>>> demonstrates your problem.
>>
>>
>> I did include that.
>>
>>>> The function looks like:
>>>> AS $$
>>>> DECLARE
>>>>   v_sql TEXT;
>>>> BEGIN
>>>>   v_sql := 'INSERT INTO messagecatalog_'||
>>>> to_char(NEW.timestampfield,'YYYY')||
>>>>   '(field1, field2) values ('
>>>> ||New.field1||','||New.field2||')
>>>> ')';
>>>>   EXECUTE v_sql;
>>>>   RETURN NULL;
>>>> END
>>>> $$;
>>
>> In the code above field1 is text and field2 is tsvector.
>
>
> Any suggestions?
> Anyone else has dealt with tsvectors in a partition environment?
> If sow how did you get the split function/rule to insert into the child
> table?
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>      choose an index scan if your joining column's datatypes do not
>      match
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83