MySQL functions for PostgreSQL

Hello

I found article about MySQL function field. PostgreSQL doesn't support similar function (it is nice together with ORDER BY expression clause), but with new variadic parameters is easy to develop it in pg:

CREATE OR REPLACE FUNCTION field(text, variadic text[])
RETURNS int AS $$
  SELECT i
     FROM generate_subscripts($2,1) g(i)
    WHERE $1 = $2[i]
    UNION ALL
    SELECT 0
    LIMIT 1
$$ LANGUAGE sql STRICT;

An using looks like:

select * from pet order by field(species, 'cat', 'dog', 'bird');

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birthday   | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

When I played with it, I ported mostly used MySQL functions to PostgreSQL. I hope, so this file can help somebody with porting applications to PostgreSQL or with better orientation in PostgreSQL. Lot of thing with strings or date PostgreSQL is done little bit different than MySQL - these functions can help.

String Functions

CREATE OR REPLACE FUNCTION concat(variadic str text[])
RETURNS text AS $$
SELECT array_to_string($1, '');
$$ LANGUAGE sql

CREATE OR REPLACE FUNCTION concat_ws(separator text, variadic str text[])
RETURNS text as $$
SELECT array_to_string($2, $1);
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
SELECT $2[$1];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[])
RETURNS int AS $$
SELECT i
FROM generate_subscripts($2,1) g(i)
WHERE $1 = $2[i]
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text)
RETURNS int AS $$
SELECT i
   FROM generate_subscripts(string_to_array($2,','),1) g(i)
  WHERE (string_to_array($2, ','))[i] = $1
  UNION ALL
  SELECT 0
  LIMIT 1
$$ LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION hex(int)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(bigint)
RETURNS text AS $$
SELECT upper(to_hex($1));
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hex(text)
RETURNS text AS $$
SELECT upper(encode($1::bytea, 'hex'))
$$ LANGUAGE sql;

/*
* char is keyword, double quotes are necessary.
*
*  postgres=# select "char"(77,121,83,81,'76');
*  char
* -------
*  MySQL
*/
CREATE OR REPLACE FUNCTION "char"(VARIADIC int[])
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT chr(unnest($1))),'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION lcase(str text)
RETURNS text AS $$
SELECT lower($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION left(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM 1 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION locate(substr text, str text)
RETURNS int AS $$
SELECT position($1 in $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION reverse(str text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT substring($1 FROM i FOR 1)
                                FROM generate_series(length($1),1,-1) g(i)),
                       '')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION right(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM length($1) - $2 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION space(n int)
RETURNS text AS $$
SELECT repeat(' ', $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION strcmp(text, text)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN -1
WHEN $1 > $2 THEN 1
ELSE 0 END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION substring_index(str text, delim text, count int)
RETURNS text AS $$
SELECT CASE WHEN $3 > 0 
THEN array_to_string((string_to_array($1, $2))[1:$3], $2)
ELSE array_to_string(ARRAY(SELECT unnest(string_to_array($1,$2))
                             OFFSET array_upper(string_to_array($1,$2),1) + $3),
                     $2)
END
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ucase(str text)
RETURNS text AS $$
SELECT upper($1)
$$ LANGUAGE sql;

CREATE CAST (bytea AS text) WITHOUT FUNCTION AS ASSIGNMENT;

/*
* SELECT hex('žlutý kůň'), unhex(hex('žlutý kůň'))
*/
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT decode($1, 'hex')::text;
$$ LANGUAGE sql;

Note: On some newer PostgreSQL the function unhex doesn't work well - you can use a second version, that works well for ascii chars or for utf8:

CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT encode(decode($1, 'hex'),'escape');
$$ LANGUAGE sql;

-- UTF8 version
CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT convert_from(decode($1, 'hex'),'utf8');
$$ LANGUAGE sql;

if you have a superuser rights, you can use a "better" auxilary function, that ensure casting from bytea to text type without changes (it's relative dirty trick):

CREATE OR REPLACE FUNCTION public.direct_bytea_to_cstring(bytea)
 RETURNS cstring
 LANGUAGE internal
 IMMUTABLE STRICT
AS $function$textout$function$

CREATE OR REPLACE FUNCTION unhex(text)
RETURNS text AS $$
SELECT direct_bytea_to_cstring(decode($1, 'hex'))::text;
$$ LANGUAGE sql;
CREATE FUNCTION

You can to try use cast (and use a original function) - you have to have a superuser rights too (this cast can be entered by postgres user only):

create cast (bytea as text) without function;

Date, time Functions

/*
* postgres=# select adddate('2008-01-02','31 day');
*   adddate
* -----------
*  2008-02-02
*/
CREATE OR REPLACE FUNCTION adddate(date, interval)
RETURNS date AS $$
SELECT ($1 + $2)::date; $$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION curdate()
RETURNS date AS $$
SELECT CURRENT_DATE
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION convert_tz(dt timestamp, from_tz text, to_tz text)
RETURNS timestamp AS $$
SELECT ($1 AT TIME ZONE $2) AT TIME ZONE $3;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date(anyelement)
RETURNS date AS $$
SELECT $1::date;
$$ LANGUAGE sql;

SELECT OR REPLACE FUNCTION datediff(date, date)
RETURNS int AS $$
SELECT $1 - $2
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_add(date, interval)
RETURNS date AS $$
SELECT adddate($1, $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(date, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_format(timestamp, text)
RETURNS text AS $$
SELECT to_char($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION date_sub(date, interval)
RETURNS date AS $$
SELECT ($1 - $2)::date;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofmonth(date)
RETURNS int AS $$
SELECT EXTRACT(day from $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION day(date)
RETURNS int AS $$
SELECT dayofmonth($1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMDay')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofweek(date)
RETURNS int AS $$
SELECT EXTRACT(dow FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION dayofyear(date)
RETURNS int AS $$
SELECT EXTRACT(doy FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_days(int)
RETURNS date AS $$
SELECT date '0001-01-01bc' + $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION from_unixtime(double precision)
RETURNS timestamp AS $$
SELECT to_timestamp($1)::timestamp
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION _mysqlf_pgsql(text)
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT s
FROM (SELECT CASE WHEN substring($1 FROM i FOR 1) <> '%'
AND substring($1 FROM i-1 FOR 1) <> '%'
THEN substring($1 FROM i for 1)
ELSE CASE substring($1 FROM i FOR 2)
WHEN '%H' THEN 'HH24'
WHEN '%p' THEN 'am'
WHEN '%Y' THEN 'YYYY'
WHEN '%m' THEN 'MM'
WHEN '%d' THEN 'DD'
WHEN '%i' THEN 'MI'
WHEN '%s' THEN 'SS'
WHEN '%a' THEN 'Dy'
WHEN '%b' THEN 'Mon'
WHEN '%W' THEN 'Day'
WHEN '%M' THEN 'Month'
END
END s
FROM generate_series(1,length($1)) g(i)) g
WHERE s IS NOT NULL),
'')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION get_format(text, text)
RETURNS text AS $$
SELECT CASE lower($1)
WHEN 'date' THEN
CASE lower($2)
WHEN 'usa' THEN '%m.%d.%Y'
WHEN 'jis' THEN '%Y-%m-%d'
WHEN 'iso' THEN '%Y-%m-%d'
WHEN 'eur' THEN '%d.%m.%Y'
WHEN 'internal' THEN '%Y%m%d'
END
WHEN 'datetime' THEN
CASE lower($2)
WHEN 'usa' THEN '%Y-%m-%d %H-.%i.%s'
WHEN 'jis' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'iso' THEN '%Y-%m-%d %H:%i:%s'
WHEN 'eur' THEN '%Y-%m-%d %H.%i.%s'
WHEN 'internal' THEN '%Y%m%d%H%i%s'
END
WHEN 'time' THEN
CASE lower($2)
WHEN 'usa' THEN '%h:%i:%s %p'
WHEN 'jis' THEN '%H:%i:%s'
WHEN 'iso' THEN '%H:%i:%s'
WHEN 'eur' THEN '%H.%i.%s'
WHEN 'internal' THEN '%H%i%s'
END
END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(time)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION hour(timestamp)
RETURNS int AS $$
SELECT EXTRACT(hour FROM $1)::int;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS $$
SELECT (date_trunc('month',$1 + interval '1 month'))::date - 1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION maketime(int, int, double precision)
RETURNS time AS $$
SELECT time '00:00:00' + $1 * interval '1 hour' + $2 * interval '1 min'
+ $3 * interval '1 sec'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION minute(timestamp)
RETURNS int AS $$
SELECT EXTRACT(minute FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION month(date)
RETURNS int AS $$
SELECT EXTRACT(month FROM $1)::int
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION monthname(date)
RETURNS text AS $$
SELECT to_char($1, 'TMMonth')
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION str_to_date(text, text)
RETURNS date AS $$
SELECT to_date($1, _mysqlf_pgsql($2))
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION time(timestamp)
RETURNS time AS $$
SELECT $1::time
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION to_days(date)
RETURNS int AS $$
SELECT $1 - '0001-01-01bc'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM current_timestamp)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp)
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION year(date)
RETURNS int AS $$
SELECT EXTRACT(year FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION week(date)
RETURNS int AS $$
SELECT EXTRACT(week FROM $1)::int;
$$ LANGUAGE sql;

GROUP_CONCAT

PostgreSQL doesn't support aggregate function group_concat. But you can use aggregate array_agg:

postgres=# select * from omega;
+---+
| x |
+---+
| 1 |
| 3 |
| 6 |
+---+
(3 rows)

postgres=# select array_to_string(array_agg(x),',') from omega;
+-----------------+
| array_to_string |
+-----------------+
| 1,3,6           |
+-----------------+
(1 row)

If you search more complete MySQL API, look on mysqlcompat project. Regards Pavel