Hello Massimo, I have psql 9.1.8; a quick console test shows replace() working fine. My read of the error is that the replace function is being handed incorrect types. Notice the following fragment in the SQL generated from the DAL (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.
