postgresql: mysql functions

mysql_functions.sql

CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITHOUT TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';

CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(TIMESTAMP WITH TIME ZONE)
RETURNS BIGINT
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint;';

CREATE OR REPLACE FUNCTION FROM_UNIXTIME(BIGINT)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE SQL
IMMUTABLE STRICT
AS 'SELECT TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * interval \'1 second\';';

-- GROUP_CONCAT:
-- data
-- id     val   
-- -----  ------
-- 1      a     
-- 2      b     
-- 2      c     

-- expected output
-- id     cnt     tmp   
-- -----  ------  ------------------
-- 1      1       a
-- 2      2       b, c

-- 1 
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

SELECT id, count(*) AS "cnt", array_to_string(array_accum(val), ', ') FROM example
GROUP BY id
ORDER BY id;

-- 2 
SELECT t.id, count(t.*) AS "cnt", array_to_string(ARRAY(SELECT val FROM example WHERE id = t.id ORDER BY val), ', ')
FROM example AS t
GROUP BY t.id
ORDER BY t.id;

-- TIMESTAMP:
CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );