MySQL functions FROM_UNIXTIME, CURDATE and UNIX_TIMESTAMP are missing in PostgreSQL. Previous versions of the module made use of those functions: if am right, the current version uses only FROM_UNIXTIME. Anyway, for your reference, the SQL code (specific to PostgreSQL) to create and drop the above functions is provided below. If the module will continue to use any of the above functions, the corresponding code should be included in statspro.install.
CREATE OR REPLACE FUNCTION curdate() RETURNS date
AS $$select current_date as result;$$
LANGUAGE sql;
DROP FUNCTION curdate();
CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp without time zone
AS $_$
SELECT $1::abstime::timestamp without time zone AS result
$_$
LANGUAGE sql;
DROP FUNCTION from_unixtime(integer);
CREATE OR REPLACE FUNCTION unix_timestamp() RETURNS integer
AS $$
SELECT
ROUND(EXTRACT( EPOCH FROM abstime(now()) ))::int4 AS result;
$$
LANGUAGE sql;
DROP FUNCTION unix_timestamp();
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS integer
AS $_$
SELECT
ROUND(EXTRACT( EPOCH FROM ABSTIME($1) ))::int4 AS result;
$_$
LANGUAGE sql;
DROP FUNCTION unix_timestamp(timestamp with time zone);
Comments
Comment #1
rsevero commentedThanks for the info.
Not being a user of PostgreSQL myself, I have to ask: defining this functions in statspro.install would be ok?
The problem I'm worried about it that statspro.install is only runned on the activation of the module, usually once per site. After that the SQL server might be restarted, migrated etc. Shouldn't this code be executed everytime I need one of these functions?
Comment #2
lifepillar commentedNo, the functions are stored procedures, so they become part of the SQL schema. A (proper) database migration will migrate those functions as well. A database restart has no effect either. They are database objects just like tables, and just like tables they can be removed only by a drop.
The only caveat is that, being PostgreSQL-specific code, in statspro.install you must add a check for the DBMS in use.
Comment #3
rsevero commentedGreat. Thanks for the detailed explanation. I'm really not used to stored procedures.
So, I just have to see how to check for the DBMS in use. Do you know how?
Comment #4
lifepillar commentedI think you may use
$GLOBALS['db_type'], which is equal to 'pgsql' if PostgreSQL is used. But you'd better check Drupal's documentation.Comment #5
drupalshrek commentedAll future development work will now only be done on the Drupal 7 version, hence I'm closing this as "won't fix".