-- vim: set ft=sql :

-- Functions for PostgreSQL
-- import from MySQL


-- DATE_FORMAT()
-- Note: Doesn't handle weeks of years yet
CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text)
RETURNS text AS $$
  DECLARE
    i int := 1;
    temp text := '';
    c text;
    n text;
    res text;
  BEGIN
    WHILE i <= pg_catalog.length($2) LOOP
      -- Look at current character
      c := SUBSTRING ($2 FROM i FOR 1);
      -- If it's a '%' and not the last character then process it as a
      -- placeholder
      IF c = '%' AND i != pg_catalog.length($2) THEN
        n := SUBSTRING ($2 FROM (i + 1) FOR 1);
        SELECT INTO res CASE
          WHEN n = 'a' THEN pg_catalog.to_char($1, 'Dy') 
          WHEN n = 'b' THEN pg_catalog.to_char($1, 'Mon') 
          WHEN n = 'c' THEN pg_catalog.to_char($1, 'FMMM') 
          WHEN n = 'D' THEN pg_catalog.to_char($1, 'FMDDth') 
          WHEN n = 'd' THEN pg_catalog.to_char($1, 'DD') 
          WHEN n = 'e' THEN pg_catalog.to_char($1, 'FMDD') 
          WHEN n = 'f' THEN pg_catalog.to_char($1, 'US') 
          WHEN n = 'H' THEN pg_catalog.to_char($1, 'HH24') 
          WHEN n = 'h' THEN pg_catalog.to_char($1, 'HH12') 
          WHEN n = 'I' THEN pg_catalog.to_char($1, 'HH12')      
          WHEN n = 'i' THEN pg_catalog.to_char($1, 'MI') 
          WHEN n = 'j' THEN pg_catalog.to_char($1, 'DDD') 
          WHEN n = 'k' THEN pg_catalog.to_char($1, 'FMHH24') 
          WHEN n = 'l' THEN pg_catalog.to_char($1, 'FMHH12') 
          WHEN n = 'M' THEN pg_catalog.to_char($1, 'FMMonth') 
          WHEN n = 'm' THEN pg_catalog.to_char($1, 'MM') 
          WHEN n = 'p' THEN pg_catalog.to_char($1, 'AM') 
          WHEN n = 'r' THEN pg_catalog.to_char($1, 'HH12:MI:SS AM') 
          WHEN n = 'S' THEN pg_catalog.to_char($1, 'SS')
          WHEN n = 's' THEN pg_catalog.to_char($1, 'SS')
          WHEN n = 'T' THEN pg_catalog.to_char($1, 'HH24:MI:SS') 
          WHEN n = 'U' THEN pg_catalog.to_char($1, '?')
          WHEN n = 'u' THEN pg_catalog.to_char($1, '?') 
          WHEN n = 'V' THEN pg_catalog.to_char($1, '?') 
          WHEN n = 'v' THEN pg_catalog.to_char($1, '?') 
          WHEN n = 'W' THEN pg_catalog.to_char($1, 'FMDay') 
          WHEN n = 'w' THEN EXTRACT(DOW FROM $1)::text
          WHEN n = 'X' THEN pg_catalog.to_char($1, '?') 
          WHEN n = 'x' THEN pg_catalog.to_char($1, '?') 
          WHEN n = 'Y' THEN pg_catalog.to_char($1, 'YYYY') 
          WHEN n = 'y' THEN pg_catalog.to_char($1, 'YY')
          WHEN n = '%' THEN pg_catalog.to_char($1, '%')
          ELSE NULL
        END;
        temp := temp operator(pg_catalog.||) res;
        i := i + 2;
      ELSE
        -- Otherwise just append the character to the string
        temp = temp operator(pg_catalog.||) c;
        i := i + 1;
      END IF; 
    END LOOP;
    RETURN temp;
  END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;


-- FROM_UNIXTIME()
-- Returns local time?  Is this actually the same as MySQL?
-- Depends on: DATE_FORMAT()

CREATE OR REPLACE FUNCTION from_unixtime(bigint)
RETURNS timestamp without time zone AS $$
  SELECT pg_catalog.to_timestamp($1)::timestamp without time zone
$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE OR REPLACE FUNCTION from_unixtime(bigint, text)
RETURNS text AS $$
  SELECT date_format(from_unixtime($1), $2)
$$ IMMUTABLE STRICT LANGUAGE SQL;


-- FROM_UNIXTIME
-- Takes a seconds-since-the-epoch integer and returns a timestamp

CREATE OR REPLACE FUNCTION FROM_UNIXTIME(BIGINT,VARCHAR)
RETURNS TIMESTAMP AS '
    SELECT ABSTIME($1) AS RESULT
' LANGUAGE SQL;

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


-- INET_NTOA()
-- done in SQL to take advantage of inlining

CREATE OR REPLACE FUNCTION inet_ntoa(bigint)
RETURNS text AS $$
SELECT CASE WHEN $1 > 4294967295 THEN NULL ELSE
    ((($1::bigint >> 24) % 256) + 256) % 256 operator(pg_catalog.||) '.' operator(pg_catalog.||)
    ((($1::bigint >> 16) % 256) + 256) % 256 operator(pg_catalog.||) '.' operator(pg_catalog.||)
    ((($1::bigint >>  8) % 256) + 256) % 256 operator(pg_catalog.||) '.' operator(pg_catalog.||)
    ((($1::bigint      ) % 256) + 256) % 256 END;
$$ IMMUTABLE STRICT LANGUAGE SQL;


-- INET_NTOA
-- Convert ip from integer

CREATE OR REPLACE FUNCTION INET_NTOA(BIGINT)
RETURNS TEXT AS '
    SELECT 
        ($1/(256*256*256))::text ||''.''||
        ($1/(256*256) - $1/(256*256*256)*256)::text ||''.''||
        ($1/256 - $1/(256*256)*256)::text ||''.''||
        ($1 - $1/256*256)::text;
' LANGUAGE SQL;

CREATE OR REPLACE FUNCTION INET_NTOA(BIGINT)
RETURNS INET AS '
DECLARE
    t inet;
BEGIN
    t = (($1>>24) & 255::int8) || ''.'' ||
        (($1>>16) & 255::int8) || ''.'' ||
        (($1>>8)  & 255::int8) || ''.'' ||
        ($1     & 255::int8);
    RETURN t;
END;
' LANGUAGE 'plpgsql';


-- INET_ATON
-- Convert ip to integer

DROP FUNCTION INET_ATON();
CREATE FUNCTION INET_ATON(TEXT) RETURNS BIGINT AS '
SELECT
        split_part($1,''.'',1)::int8*(256*256*256)+
        split_part($1,''.'',2)::int8*(256*256)+
        split_part($1,''.'',3)::int8*256+
        split_part($1,''.'',4)::int8;
' LANGUAGE 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;';