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.
