Now I understand. WIll fix asap. On Monday, 15 April 2013 10:12:50 UTC-5, Ricardo Cárdenas wrote: > > Hello Massimo, > > I have psql 9.1.8; a quick console test shows replace() working fine. My > read of the error is that replace() is being handed an incorrect > datatype. Notice the following fragment from the DAL-generated SQL (posted > below): > > REPLACE(my_group.id,'%','%%') > > The first argument is an integer. I assume replace() cannot handle that. > best regards -Ricardo > > > On Monday, April 15, 2013 9:56:53 AM UTC-5, Massimo Di Pierro wrote: >> >> Which postgresql version are you using? It is complain there is not >> replace function but there should be: >> >> http://www.postgresql.org/docs/8.1/static/functions-string.html >> >> On Saturday, 13 April 2013 15:23:04 UTC-5, Ricardo Cárdenas wrote: >>> >>> Hi niphlod, here's the code: >>> >>> p = db.my_post >>> g = db.my_group >>> m = db.my_membership >>> query = ((m.my_user == auth.user.id) & >>> (m.my_group == g.id) & >>> (p.groups.contains(g.id))) >>> rows= db(query).select(p.id, p.status, orderby=~p.creation_date,distinct >>> =True) >>> >>> The SQL generated is: >>> >>> SELECT DISTINCT my_post.id, my_post.status FROM my_post, >>> my_membership,my_group WHERE >>> (((my_membership.my_user = 1) AND (my_membership.my_group = my_group.id >>> )) AND (CAST(my_post.groups AS CHAR(512)) LIKE (('%|' || (REPLACE(( >>> REPLACE(my_group.id,'%','%%')),'|','||')) || '|%')))) ORDER BY >>> my_post.creation_date >>> DESC; >>> >>> and the error again: >>> >>> <class 'psycopg2.ProgrammingError'> function replace(integer, unknown, >>> unknown) does not exist LINE 1: ...ost.groups AS CHAR(512)) LIKE (('%|' || >>> (REPLACE((REPLACE(me... ^ HINT: No function matches the given name and >>> argument types. You might need to add explicit type casts. >>> >>> Thanks and best regards -Ricardo >>> >>> >>> On Saturday, April 13, 2013 10:42:52 AM UTC-5, Niphlod wrote: >>>> >>>> can you post the code that generates the error and the query generated >>>> bu the DAL ( i.e. print db(......)._select() with the underscore) ? >>>> >>>> On Saturday, April 13, 2013 4:28:55 PM UTC+2, Ricardo Cárdenas wrote: >>>>> >>>>> Just upgraded to 2.4.6-stable+timestamp.2013.04.12.23.32.51. >>>>> >>>>> Code that worked in my prior version (I think trunk 2013.04.02? sorry >>>>> don't have the exact version on-hand) now breaks. I get the following >>>>> error: >>>>> >>>>> <class 'psycopg2.ProgrammingError'> function replace(integer, >>>>> unknown, unknown) does not exist LINE 1: ...ost.groups AS CHAR(512)) LIKE >>>>> (('%|' || (REPLACE((REPLACE(me... ^ HINT: No function matches the given >>>>> name and argument types. You might need to add explicit type casts >>>>> >>>>> The ticket comes from a 3-table query on PostgreSQL that uses >>>>> .CONTAINS. (Perhaps related to this >>>>> issue<https://groups.google.com/forum/?fromgroups=#!searchin/web2py/contains/web2py/WKmzK6prO20/SRkZ2SF5YlwJ>.) >>>>> >>>>> When I simplify the query to a simple db(table).select(), the code works >>>>> (though of course it doesn't bring back the results I am looking for). >>>>> >>>>> I just thought I'd throw it out there in case someone has worked on >>>>> that code recently.. >>>>> >>>>> best regards -Ricardo >>>>> >>>>
-- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
