Forum OpenACS Q&A: sysdate() problem with OpenAcs 4
running OpenAcs 3.2.5 (thanks Jonathan!) and so I did nothing about
Aolserver and PostgreSQL. The installation was good and I can
connect and navigate into the site. I tried to exec some working
scripts until I got an (apparently) incredible error: PostgreSQL
doesn't recognize the sysdate() function:
openacs-4=# select sysdate();
ERROR: Function 'sysdate()' does not exist
Unable to identify a function that satisfies the given
You may need to add explicit typecasts
Note that if I open psql with the DB used by OpenAcs 3.2.5 all is OK.
-- sysdate hack to make things look somewhat alike create function sysdate() returns datetime as ' begin return ''now''; end;' language 'plpgsql';
Ok this works in psql:
mydata=# select now() from dual; now ------------------------ 2001-10-24 14:51:05-04 (1 row)
But this does not:
mydata=# select current_timestamp() from dual; ERROR: parser: parse error at or near ")"
I guess I'll stick with the here and NOW() !
openacs4=# select current_timestamp; timestamp ------------------------ 2001-10-24 16:00:25-04 (1 row)
The reason why sysdate() works in your 3.2.5 database, is because that function is defined in the 3.2.5 datamodel, but it probably isn't defined in the 4.2 datamodel. The functions of one database are not transplanted to another.
As others have noted, it's better to use the standard CURRENT_TIMESTAMP.