Re: 9.5: tsvector problem

Hello Johann

There are two to_tsvector functions:

charles(at)charles(dot)[local]=# \df to_tsvector
List of functions
Schema | Name | Result data type | Argument data types |
Type
------------+-------------+------------------+---------------------+--------
pg_catalog | to_tsvector | tsvector | regconfig, text | normal
pg_catalog | to_tsvector | tsvector | text | normal
(2 rows)

I think that you may try casting the result of coalesce() to TEXT.

Bye
Charles

On 04/26/2016 03:25 PM, Johann Spies wrote:
> I have never seen this problem before. It occurred while trying to
> import a dump (done by 9.5 client of a 9.4 database) also.
>
> Table definition:
>
> -
> CREATE TABLE source.annual
> (
> filename text,
> gzipfile text,
> id serial NOT NULL,
> tsv tsvector,
> ut character varying(19),
> xml xml,
> processed boolean,
> CONSTRAINT annual_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS=FALSE
> );
> CREATE INDEX xml_tsv_idx
> ON source.annual
> USING gin
> (tsv);
>
>
> CREATE TRIGGER tsvectorupdate_source_xml
> BEFORE INSERT OR UPDATE
> ON source.annual
> FOR EACH ROW
> EXECUTE PROCEDURE source.update_xml_tsv();
>
>
> And the trigger function:
>
> CREATE OR REPLACE FUNCTION source.update_xml_tsv()
> RETURNS trigger AS
> $BODY$
>
> begin
>
> new.tsv := to_tsvector('english', coalesce(new.xml,''));
>
> return new;
>
> end
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
>
>
> When I try to insert a value into this table, I get:
> ==================================
> ERROR: function to_tsvector(unknown, xml) does not exist
> LINE 1: SELECT to_tsvector('english', coalesce(new.xml))
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY: SELECT to_tsvector('english', coalesce(new.xml))
> CONTEXT: PL/pgSQL function source.update_xml_tsv() line 5 at assignment
>
> ********** Error **********
>
> ERROR: function to_tsvector(unknown, xml) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might
> need to add explicit type casts.
> Context: PL/pgSQL function source.update_xml_tsv() line 5 at assignment
> =============================================
>
> But I can do
>
> select to_tsvector('English', 'This is a problem')
>
> without a problem.
>
> What is causing this?
>
> Regards
> Johann
>
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)

--
----------------------------
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org