BUG #3848: function pg_catalog.substring(date, integer, integer) does not exist

The following bug has been logged online:

Bug reference: 3848
Logged by: Ion
Email address: sorry_not_for_spam(at)yahoo(dot)fr
PostgreSQL version: 8.3
Operating system: Windows XP SP2
Description: function pg_catalog.substring(date, integer, integer)
does not exist
Details:

Hello,

I tested PostgreSQL 8.3 beta4 with tinyerp and I have this problem:
pg_catalog.substring(date, integer, integer) does not exist
The traceback of python 2.4 :
======================================================
Traceback (most recent call last):
File "D:\erp\tinyerp-server-4.2.1\bin\netsvc.py", line 200, in _dispatch
r=m(*params)
File "D:\erp\tinyerp-server-4.2.1\bin\service\web_services.py", line 422,
in execute
return self._execute(db, uid, wiz_id, datas, action, context)
File "D:\erp\tinyerp-server-4.2.1\bin\service\web_services.py", line 402,
in _execute
return wiz.execute(db, uid, self.wiz_datas[wiz_id], action, context)
File "D:\erp\tinyerp-server-4.2.1\bin\wizard\__init__.py", line 160, in
execute
res = self.execute_cr(cr, uid, data, state, context)
File "D:\erp\tinyerp-server-4.2.1\bin\wizard\__init__.py", line 75, in
execute_cr
action_res = action(self, cr, uid, data, context)
File
"D:\erp\tinyerp-server-4.2.1\bin\addons\base_setup\wizard\wizard_base_setup.
py", line 220, in _update
(db, pool)=pooler.restart_pool(cr.dbname, update_module=True)
File "D:\erp\tinyerp-server-4.2.1\bin\pooler.py", line 64, in
restart_pool
return get_db_and_pool(db_name, force_demo,
update_module=update_module)
File "D:\erp\tinyerp-server-4.2.1\bin\pooler.py", line 53, in
get_db_and_pool
addons.load_modules(db, force_demo, status, update_module)
File "D:\erp\tinyerp-server-4.2.1\bin\addons\__init__.py", line 274, in
load_modules
load_module_graph(cr, graph, status, report=report)
File "D:\erp\tinyerp-server-4.2.1\bin\addons\__init__.py", line 204, in
load_module_graph
init_module_objects(cr, m, modules)
File "D:\erp\tinyerp-server-4.2.1\bin\addons\__init__.py", line 181, in
init_module_objects
obj.init(cr)
File
"D:\erp\tinyerp-server-4.2.1\bin\addons\report_analytic_line\report_analytic
_line.py", line 48, in init
cr.execute("""
File "D:\erp\tinyerp-server-4.2.1\bin\sql_db.py", line 75, in execute
res = self.obj.execute(sql)
ProgrammingError: ERROR: function pg_catalog.substring(date, integer,
integer) does not exist at character 134
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.

1
======================================================

I have not this problem with postgresql 8.2.5.

Thanks.
I happy New Year !

On Tue, Jan 01, 2008 at 08:11:01PM +0000, Ion wrote:
> Email address: sorry_not_for_spam(at)yahoo(dot)fr

lets hope Ion is subscribed! :)

> I tested PostgreSQL 8.3 beta4 with tinyerp and I have this problem:
> pg_catalog.substring(date, integer, integer) does not exist

This is most likely a bug in tinyerp. substring has only ever been
defined for string data types and isn't valid for date types at all.

> I have not this problem with postgresql 8.2.5.

Prior to PG 8.3, all types would be silently coerced into TEXT types in
certain cases, leading to all sorts of strangeness.

I've just downloaded the source of tinyerp and had a look though and
this practice seems pretty endemic. They should really be using

to_char(date_exp, 'YYYY-MM')

instead of

substring(date_exp FOR 7)

in all their queries.

Sam

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> I've just downloaded the source of tinyerp and had a look though and
> this practice seems pretty endemic. They should really be using
> to_char(date_exp, 'YYYY-MM')
> instead of
> substring(date_exp FOR 7)
> in all their queries.

Or at least explicitly casting the date to text. But your way is
better, since it won't break if the datestyle is something other
than ISO.

regards, tom lane

On Tue, Jan 01, 2008 at 04:29:47PM -0500, Tom Lane wrote:
> Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> > I've just downloaded the source of tinyerp and had a look though and
> > this practice seems pretty endemic. They should really be using
> > to_char(date_exp, 'YYYY-MM')
> > instead of
> > substring(date_exp FOR 7)
> > in all their queries.
>
> Or at least explicitly casting the date to text. But your way is
> better, since it won't break if the datestyle is something other
> than ISO.

It also improves code readability is is always a good thing.

I've just gone though the code and sent a patch to the tinyerp-devel
list with these fixes in. Lets see if it gets incorporated.

Sam