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.


No replies to this topic
Also tagged with one or more of these keywords: postgres, pg, search
Support →
I have a question →
Change deafult text in search boxes in 'datatables' themeStarted by Juanma, 12 Sep 2019 ![]() |
|
![]() |
||
Support →
Grocery CRUD Enterprise →
Has anyone worked with multi schemes of Postgres in GCE?Started by Cyn, 26 Aug 2019 ![]() |
|
![]() |
||
Support →
Grocery CRUD Enterprise →
Disable dropdown in search filters and change to text inputStarted by Indeax, 26 Nov 2018 ![]() |
|
![]() |
||
Support →
Bugs / Issues →
A PHP Error was encounteredStarted by matteo94s, 16 Oct 2018 ![]() |
|
![]() |
||
Support →
I have a question →
GC with PostgresStarted by guillermovil, 10 Oct 2018 ![]() |
|
![]() |
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users