PostgreSQL: ERROR: function curdate() does not exist

[j0inty]

Hi,

if you to run the dashboard with postgresql backend you will geht the following error.

NC: 12.0.3.3
Dashboard: 4.0.5

ErrorMessage:
Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing 'SELECT * FROM \"oc_dashboard_announcements\" WHERE expiration >= CURDATE() ORDER BY created_at DESC LIMIT 5':\n\nSQLSTATE[42883]: Undefined function: 7 ERROR: function curdate() does not exist\nLINE 1: ... \"oc_dashboard_announcements\" WHERE expiration >= CURDATE() ...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts."

Full Error Exception Message from syslog:
Sep 22 08:52:29 myHost ownCloud[1595]: {index} Exception: {"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing 'SELECT * FROM \"oc_dashboard_announcements\" WHERE expiration >= CURDATE() ORDER BY created_at DESC LIMIT 5':\n\nSQLSTATE[42883]: Undefined function: 7 ERROR: function curdate() does not exist\nLINE 1: ... \"oc_dashboard_announcements\" WHERE expiration >= CURDATE() ...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.","Code":0,"Trace":"#0 \/var\/www\/nextcloud-12.0.3\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(128): Doctrine\\DBAL\\Driver\\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\\DBAL\\Driver\\PDOException))\n#1 \/var\/www\/nextcloud-12.0.3\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Statement.php(177): Doctrine\\DBAL\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver), Object(Doctrine\\DBAL\\Driver\\PDOException), 'SELECT * FROM \"...', Array)\n#2 \/var\/www\/nextcloud-12.0.3\/lib\/public\/AppFramework\/Db\/Mapper.php(247): Doctrine\\DBAL\\Statement->execute()\n#3 \/var\/www\/nextcloud-12.0.3\/lib\/public\/AppFramework\/Db\/Mapper.php(332): OCP\\AppFramework\\Db\\Mapper->execute('SELECT * FROM...', Array, 5, NULL)\n#4 /var/www/nextcloud-12.0.3/apps/dashboard/lib/Db/AnnouncementMapper.php(49): OCP\AppFramework\Db\Mapper->findEntities('SELECT * FROM ...', Array, 5, NULL)\n#5 \/var\/www\/nextcloud-12.0.3\/apps\/dashboard\/lib\/Controller\/AnnouncementController.php(242): OCA\\Dashboard\\Db\\AnnouncementMapper->findAll(5)\n#6 [internal function]: OCA\\Dashboard\\Controller\\AnnouncementController->index()\n#7 \/var\/www\/nextcloud-12.0.3\/lib\/private\/AppFramework\/Http\/Dispatcher.php(160): call_user_func_array(Array, Array)\n#8 \/var\/www\/nextcloud-12.0.3\/lib\/private\/AppFramework\/Http\/Dispatcher.php(90): OC\\AppFramework\\Http\\Dispatcher->executeController(Object(OCA\\Dashboard\\Controller\\AnnouncementController), 'index')\n#9 \/var\/www\/nextcloud-12.0.3\/lib\/private\/AppFramework\/App.php(114): OC\\AppFramework\\Http\\Dispatcher->dispatch(Object(OCA\\Dashboard\\Controller\\AnnouncementController), 'index')\n#10 \/var\/www\/nextcloud-12.0.3\/lib\/private\/AppFramework\/Routing\/RouteActionHandler.php(47): OC\\AppFramework\\App::main('OCA\\\\Dashboard\\\\C...', 'index', Object(OC\\AppFramework\\DependencyInjection\\DIContainer), Array)\n#11 [internal function]: OC\\AppFramework\\Routing\\RouteActionHandler->__invoke(Array)\n#12 \/var\/www\/nextcloud-12.0.3\/lib\/private\/Route\/Router.php(299): call_user_func(Object(OC\\AppFramework\\Routing\\RouteActionHandler), Array)\n#13 \/var\/www\/nextcloud-12.0.3\/lib\/base.php(1004): OC\\Route\\Router->match('\/apps\/dashboard...')\n#14 \/var\/www\/nextcloud-12.0.3\/index.php(48): OC::handleRequest()\n#15 {main}","File":"\/var\/www\/nextcloud-12.0.3\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractPostgreSQLDriver.php","Line":91}

[elsbiet]

workaround: login to nextcloud's database with nextcloud's userid and define curdate().

-- postgres only: just to make nextcloud's dashboard happy
-- (this function is part of mysql standard but not part of postgres' standard)
CREATE or replace FUNCTION curdate() RETURNS date AS $$
    BEGIN
        RETURN current_date;
    END; $$
LANGUAGE PLPGSQL;