Реализация MySQL функции field для PostgreSQL, позволяющей организовать условную сортировку вывода:

   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;

Результат использования:

   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       |

Результаты портирования некоторых строковых функций MySQL, которые можно использовать для упрощения
переноса программ с MySQL на PostgreSQL:

concat

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

concat_ws

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

elt

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

find_in_set

   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;

hex

   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, напирмер: select "char"(77,121,83,81,'76');

   CREATE OR REPLACE FUNCTION "char"(VARIADIC int[])
   ETURNS text AS $$
   SELECT array_to_string(ARRAY(SELECT chr(unnest($1))),'')
   $$ LANGUAGE sql;

lcase

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

left

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

locate

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

reverse

   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;

right

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

space

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

strcmp

   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;

substring_index

   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;

ucase

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

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

unhex

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

Функции для работы с датой и временем:

adddate, пример select adddate('2008-01-02','31 day');

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

curdate

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

convert_tz

   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;

date

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

datediff

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

date_add

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

date_format

   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;

date_sub

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

dayofmonth

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

day

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

dayname

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

dayofweek

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

dayofyear

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

from_days

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

from_unixtime

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

_mysqlf_pgsql

   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;

get_format

   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;

hour

   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;

last_day

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

makedate

   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;

maketime

   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;

minute

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

month

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

monthname

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

str_to_date

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

time

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

to_days

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

unix_timestamp

   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;

year

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

week

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

Эмуляция работы GROUP_CONCAT в PostreSQL:

   select array_to_string(array_agg(x),',') from omega;

Дополнительно можно обратить внимание на проект http://pgfoundry.org/projects/mysqlcompat/ , в рамках которого
ведется работа по портированию для PostgreSQL некоторых MySQL функций.

URL: http://okbob.blogspot.com/2009/08/mysql-functions-for-postgr...
Обсуждается: https://www.opennet.ru/tips/info/2148.shtml