No replies to this topic

[tkalfigo]

After using jcasanova's code to adapt GC for Postgres (http://www.grocerycr...in-postgres-91/) all works perfect with Pg (v9.0) except that there is an issue with "Search".

It doesn't work when you search against a non-text field. For example if you search against an integer field it raises the error:
ERROR:  operator does not exist: integer ~~ text
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

This happens because the SQL is of the form:
SELECT COUNT(*) AS "numrows"
FROM "tabname"
WHERE  "integer_field"  LIKE '%search_string%' ESCAPE '!'

and the LIKE operator doesn't handle an integer argument ('integer LIKE text' in this case). The LIKE in Pg is the same operator as ~~ and the solution is to overload this operator by creating one that handles arguments {"non-text" + text}.

For example to handle searches against integers:
CREATE OPERATOR PUBLIC.~~ (LEFTARG=integer, RIGHTARG=text, PROCEDURE=my_like);
Where the definition of the my_like() stored procedure is:
CREATE OR REPLACE FUNCTION PUBLIC.my_like(leftop integer, rightop text)
RETURNS boolean
LANGUAGE sql
AS $function$
SELECT $1::text LIKE $2;
$function$;

Likewise both the stored procedure and the operator have to be implemented for double precision argument and any other non-text argument you may have to search against.

best regards,
Thalis K.