Pages

Saturday, August 22, 2009

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

17 comments:

  1. Very nice! I bookmarked this for future reference. In addition to making mysql expatriates' lives easier, these are good examples of postgresql functions.

    ReplyDelete
  2. I append link on http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Documentation

    ReplyDelete
  3. Actually no, this should be rolled up into mysqlcompat (imho). http://pgfoundry.org/projects/mysqlcompat/

    ReplyDelete
  4. to Robert: This article is much more doc for mysql people then compatibility project

    ReplyDelete
  5. To Robert: I looked on mysqlcompat project. I thing, so when "parser hook" patch will be commited, then I can rewrite it to C.

    ReplyDelete
  6. Looks like you've got an extraneous END in your lcase function.

    ReplyDelete
  7. Awesome post! Saved me hours of changing code !

    ReplyDelete
  8. Couple things about the unhex function:

    * It returns test. I think it needs to return an integer.

    * It reads the args as hex, but returns the result in octal? My PG version is 8.4 from ubuntus apt.

    test=# CREATE OR REPLACE FUNCTION unhex(text)
    RETURNS text AS $$
    SELECT decode($1, 'hex')::text;
    $$ LANGUAGE sql;
    CREATE FUNCTION
    test=# select unhex('10');
    unhex
    -------
    \020
    (1 row)

    test=# select unhex('08');
    unhex
    -------
    \010
    (1 row)

    ReplyDelete
  9. to Denimboy

    The original functions working on 8.3. I wrote a note for newer Pg and solution that works (if you has no postgres rights) and works well (if you has "postgres" rights).

    Returned type "text" is correct - look to MySQL's documentation

    ReplyDelete
  10. Unless I'm missing the point, CONVERT_TZ should be

    SELECT ($1 AT TIME ZONE $2)::timestamptz AT TIME ZONE $3;

    Cheers!

    ReplyDelete
  11. A version of GROUP_CONCAT that supports integer arguments (so that you don't have to rewrite a bunch of mySQL queries you might be porting...):

    -- group_concat.sql

    -- permutation of GROUP_CONCAT parameter types with delimiter parameter furnished:
    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 TEXT, delimiter TEXT)
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(column1||delimiter||column2, column2, column1);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8, delimiter TEXT)
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(column1||delimiter||CAST(column2 AS TEXT), CAST(column2 AS TEXT), column1);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT, delimiter TEXT)
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(CAST(column1 AS TEXT)||delimiter||column2, column2, CAST(column1 AS TEXT));
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8, delimiter TEXT)
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(CAST(column1 AS TEXT)||delimiter||CAST(column2 AS TEXT), CAST(column2 AS TEXT), CAST(column1 AS TEXT));
    END;
    $$ LANGUAGE plpgsql;

    -- permutation of function arguments without delimiter furnished:
    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 TEXT) -- delimiter=','
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(column1||','||column2, column2, column1);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 INT8) -- delimiter=','
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(CAST(column1 AS TEXT)||','||CAST(column2 AS TEXT), CAST(column2 AS TEXT), CAST(column1 AS TEXT));
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 INT8, column2 TEXT) -- delimiter=','
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(CAST(column1 AS TEXT)||','||column2, column2, CAST(column1 AS TEXT));
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE
    FUNCTION GROUP_CONCAT_ATOM(column1 TEXT, column2 INT8) -- delimiter=','
    RETURNS TEXT AS $$
    BEGIN
    RETURN COALESCE(column1||','||CAST(column2 AS TEXT), CAST(column2 AS TEXT), column1);
    END;
    $$ LANGUAGE plpgsql;

    -- aggregates for all parameter types with delimiter:
    DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT, TEXT); -- column, delimiter
    CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) -- column, delimiter
    (SFUNC=GROUP_CONCAT_ATOM,
    STYPE=TEXT
    );

    DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8, TEXT); -- column, delimiter
    CREATE AGGREGATE GROUP_CONCAT(INT8, TEXT) -- column
    (SFUNC=GROUP_CONCAT_ATOM,
    STYPE=TEXT
    );

    -- aggregates for all parameter types without the optional delimiter:
    DROP AGGREGATE IF EXISTS GROUP_CONCAT(TEXT); -- column, delimiter=','
    CREATE AGGREGATE GROUP_CONCAT(TEXT) -- column, delimiter=','
    (SFUNC=GROUP_CONCAT_ATOM,
    STYPE=TEXT
    );

    DROP AGGREGATE IF EXISTS GROUP_CONCAT(INT8); -- column, delimiter=','
    CREATE AGGREGATE GROUP_CONCAT(INT8) -- column, delimiter=','
    (SFUNC=GROUP_CONCAT_ATOM,
    STYPE=TEXT
    );

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete
  13. -- foreign-key-checks.sql

    CREATE OR REPLACE FUNCTION foreign_key_checks(enable boolean)
    RETURNS INT8 AS
    $$
    DECLARE public_table RECORD;
    BEGIN
    FOR public_table
    IN SELECT tablename
    FROM pg_catalog.pg_tables
    WHERE schemaname='public' AND hastriggers=TRUE
    LOOP
    EXECUTE 'ALTER TABLE '||public_table.tablename||' '||
    (CASE WHEN enable THEN 'EN' ELSE 'DIS' END)||
    'ABLE TRIGGER ALL;';
    END LOOP;
    RETURN (SELECT COUNT(tablename)
    FROM pg_catalog.pg_tables
    WHERE schemaname='public' AND hastriggers=TRUE
    );
    END;
    $$ LANGUAGE plpgsql;

    ReplyDelete
  14. Minor update to remove an unnecessary SELECT COUNT...

    -- foreign_key_checks.sql
    CREATE OR REPLACE FUNCTION foreign_key_checks(enable boolean)
    RETURNS INT8 AS
    $$
    DECLARE public_table RECORD;
    DECLARE count INT8;
    BEGIN
    count=0;
    FOR public_table
    IN SELECT tablename
    FROM pg_catalog.pg_tables
    WHERE schemaname='public' AND hastriggers=TRUE
    LOOP
    EXECUTE 'ALTER TABLE '||public_table.tablename||' '||
    (CASE WHEN enable THEN 'EN' ELSE 'DIS' END)||
    'ABLE TRIGGER ALL;';
    count=count+1;
    END LOOP;
    RETURN count;
    END;
    $$ LANGUAGE plpgsql;

    ReplyDelete
  15. Nice article Get here updated some more inf about PostgreSQL vs MySQL 2016 thanks

    ReplyDelete