Oleg Bartunov suggested that I should explicitly use
set search_path=public,pg_catalog;
That's a good tip, as I missed the info in the manual, but it solved
only one instance of my problem, namely
SELECT to_tsquery('cat & dog & mouse'); --this now works as it should
But the other instances still use the default INPUT/OUTPUT functions
and are not affected by schema setting:
SELECT 'cat & dog & mouse'::tsquery; --uses the built in function
SELECT CAST('dogs & cat' AS tsquery); --uses the built in function
SELECT public.to_tsquery('dog " mouse'); --new operator (doublequote),
works fine until OUTPUT function is called and as it is the default
one an error is displayed
Is there any other way I could overwrite the default functions for a
built in type in Postgres?
2010/4/9 Łukasz Dejneka <l.dejneka@gmail.com>:
> Hi all
>
> I've asked related question on General list, but got no answer,
> although I have been able to work around that issue a little bit.
> There is one snag I encountered and I have no idea on how to work it
> out.
>
> I need to modify TSQuery object (add another operator and do some
> stuff with it) - this is done and works properly at C code level. Now
> I need to implement changes done in C on PG level. I really do not
> want to make another type, tsquery2 or such...
>
> What I've tried:
> - the manual states that you can create your own data types with
> CREATE TYPE command and alter some of their proprieties with ALTER
> TYPE. But it is not possible to modify INPUT or OUTPUT function.
> - I have created updated PG versions of the functions to_tsquery (the
> CAST function) and tsqueryout (the TYPE OUTPUT) function and they are
> in the "public" schema.
> - I have created a CAST from text to tsquery pointing to
> public.to_tsquery function.
> - I have changed the search_path so the "public" schema is first.
>
> How do I overwrite the default behaviour of Postgres 8.4, so I can
> successfully run the following queries:
>
> --1.
> SELECT 'cat & dog & mouse'::tsquery; --uses the built in function
> SELECT to_tsquery('cat & dog & mouse'); --also uses the built in function
> --but
> SELECT public.to_tsquery('cat & dog & mouse'); --uses new functions
>
> --2.
> SELECT CAST('dogs & cat' AS tsquery); --uses the built in function
> --but
> SELECT CAST('dogs & cat'::text AS tsquery); --uses new functions
>
> --3.
> SELECT public.to_tsquery('dog " mouse'); --new operator (doublequote),
> works fine until OUTPUT function is called and as it is the default
> one an error is displayed
> --but
> SELECT public.tsqueryout(CAST('dogs " cat'::text AS tsquery)); --uses
> new functions and displays correctly
>
> I thought that setting schema so "public" has priority over all other
> would make PG use those functions in the first place. Also is the
> string between the single quotes in SQL commands not treated as text
> type? Is this why a CAST to ::text make it work in example 2?
>
> Thanks in advance.
>
