Jump to content


"Search" workaround for Postgres

postgres pg search

  • Please log in to reply
No replies to this topic

#1 tkalfigo

tkalfigo

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 30 October 2012 - 11:48 PM

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.





Also tagged with one or more of these keywords: postgres, pg, search

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users