Re: 9.5: tsvector problem

Hello Johann

There are two to_tsvector functions:

charles@charles.[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