URL: https://www.opennet.me/cgi-bin/openforum/vsluhboard.cgi
Форум: vsluhforumID3
Нить номер: 58191
[ Назад ]

Исходное сообщение
"Раздел полезных советов: Реализация для PostgreSQL некоторых..."
Отправлено auto_tips , 25-Авг-09 15:59

Реализация 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...
Обсуждается: http://www.opennet.me/tips/info/2148.shtml

Содержание

Реализация для PostgreSQL некоторых популярных функций из состава MySQL,Vitaly_loki, 15:59 , 25-Авг-09
Реализация для PostgreSQL некоторых популярных функций из состава MySQL,ogromnoe spasibo, 03:15 , 06-Апр-10

Сообщения в этом обсуждении

"Реализация для PostgreSQL некоторых популярных функций из состава MySQL"
Отправлено Vitaly_loki , 25-Авг-09 15:59

А зачем вот нужны функции типа этой:
day
   CREATE OR REPLACE FUNCTION day(date)
   RETURNS int AS $$
   SELECT dayofmonth($1)
   $$ LANGUAGE sql;

не проще ли тупо сделать SELECT dayofmonth($1)?
Или это чтоб SQL-запросы одинаково работали как на мускуле, так и на слонах?

"Реализация для PostgreSQL некоторых популярных функций из состава MySQL"
Отправлено ogromnoe spasibo , 06-Апр-10 03:15

однозначно в меморис