Přechod z MySQL
MySQL a PostgreSQL jsou dvě různé databáze s rozdílnou filozofií a samozřejmě i rozdílným uživatelským rozhraním. V tomto dokumentu byste měli najít tipy, které Vám usnadní přechod z MySQL do PostgreSQL (a i případně naopak).
Prvotní přihlášení, vytvoření uživatele
Po instalaci PostgreSQL je vytvořen pouze jediný databázový účet a to účet postgres (Platí pro Unix). Uživatel root nemá přístup k databázi. Uživatel postgres má plnou kontrolu nad databází. Jedná se o tzv. superuživatele. Nedoporučuje se běžně používat tohoto uživatele (má příliš velká práva), vhodnější je vytvořit si vlastní účet.
[root@localhost ~]# su postgres bash-3.2$ createuser tomas could not change directory to "/root" Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n bash-3.2$ exit [root@localhost ~]# exit [pavel@localhost ~]$ psql -U tomas postgres Timing is on. psql (8.4beta1) Type "help" for help. postgres=> SELECT current_user; current_user -------------- tomas (1 row) Time: 36,533 ms postgres=> \q
Pod uživatelem root se k databázi nepřipojíte!
Konzole
SQL konzole se v PostgreSQL spouští příkazem psql. Parametrem tohoto příkazu je název databáze, ke které se chcete přihlásit. Na rozdíl od MySQL vždy musíte uvést název databáze. Pokud nespecifikujete databázi, psql se zkouší připojit k databázi, jejíž jméno odpovídá Vašemu uživatelskému účtu. Obyčejně taková databáze neexistuje. Pokud ještě nemáte vytvořenou svou databázi, můžete se připojit do databáze postgres. V konzoli pak vytvoříte databázi příkazem CREATE DATABASE nazev. Další možností je spustit program createdb.
V konzoli můžeme zadávat SQL příkazy nebo tzv. metapříkazy. Jednoduše řečeno, metapříkazy jsou příkazy pro konzolu, SQL příkazy se posílají na databázový server. Takovým zásadním metapříkazem je příkaz \q, kterým se ukončuje konzole. Musím dodat, všechny metapříkazy začínají zpětným lomítkem a následuje několik písmen, které specifikují (upřesňují) smysl příkazu. V podstatě se jedná o analogii ovládání editoru vi (vim). Pouze místo dvojtečky je zpětné lomítko "\". Metapříkazy se používají i pro zobrazení struktury tabulek, seznamu tabulek, zobrazení nápovědy k SQL příkazům. Seznam metapříkazů zobrazí metapříkaz \?.
[pavel@localhost ~]$ psql postgres
Timing is on.
psql (8.4beta1)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# CREATE DATABASE test;
CREATE DATABASE
Time: 4145,002 ms
výpis databází (metapříkaz \l)
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
template0 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres
: postgres=CTc/postgres
test | pavel | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
(4 rows)
Připojení se k databázi test (metapříkaz \c) a vytvoření a plnění tabulky foo:
postgres=# \c test psql (8.4beta1) You are now connected to database "test". test=# CREATE TABLE foo(a integer); CREATE TABLE Time: 9,318 ms test=# INSERT INTO foo VALUES(10),(20); INSERT 0 2 Time: 2,759 ms test=# SELECT * FROM foo; a ---- 10 20 (2 rows) Time: 1,102 ms
Seznam tabulek (metapříkaz \dt) a detail tabulky (metapříkaz \d)
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | pavel
(1 row)
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
test=#
Konec práce (metapříkaz \q)
test=# \q [pavel@localhost ~]$
Zásadní rozdíl mezi MySQL a PostgreSQL je v chování příkazu SHOW. V MySQL se tento příkaz používá k zobrazení struktury tabulek, k zobrazení seznamu tabulek. Naopak, v PostgreSQL, se pro tento účel používají buďto metapříkazy, nebo SQL dotazy do informačního schématu. Příkaz SHOW, v PostgreSQL, slouží pouze k zobrazení obsahu systémových proměnných databáze.
[pavel@localhost ~]$ psql test
Timing is on.
psql (8.4beta1)
Type "help" for help.
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
test=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'foo';
column_name | data_type
-------------+-----------
a | integer
(1 row)
Time: 23,149 ms
test=# \q
Tabulku zachycující odpovídající si příkazy MySQL a PostgreSQL naleznete na adrese http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.html.
Identifikátory a řetězce
Ohledně zápisu identifikátorů a řetězců se PostgreSQL relativně hodně drží standardu ANSI SQL, což znamená, že pro zápis řetězců se používají apostrofy a pro zápis nestandardních identifikátorů uvozovky.
Identifikátory v MySQL jsou case insensitive a nesmějí obsahovat některé znaky (např. mezeru nebo tečku). Pokud takový identifikátor potřebujeme, použijeme uvozovky. Pozn. Zásadně nedoporučuji používat takové identifikátory pro názvy tabulek nebo sloupců. Dejte si pozor v případě různých GUI, které automaticky používají uvozovky pro identifikátory. V kombinaci s názvy, obsahujícími jak malá, tak velká písmena, je to příčina obtíží při psaní SQL dotazů. MySQL používá obrácený apostrof, a na rozdíl od PostgreSQL je zápis identifikátorů v apostrofech poměrně rozšířený.
Pro zápis řetězců se v MySQL používají uvozovky i apostrofy. Podobně jako v C nebo v Javě:
mysql> select 'xxxx' as Test; +------+ | Test | +------+ | xxxx | +------+ 1 row in set (0.00 sec) mysql> select "xxxx" as `Test ss`; +---------+ | Test ss | +---------+ | xxxx | +---------+ 1 row in set (0.00 sec) mysql> select 'xxxx' as `Test ss`; +---------+ | Test ss | +---------+ | xxxx | +---------+ 1 row in set (0.00 sec)
V PostgreSQL:
postgres=# select 'xxxx' as Test; test ------ xxxx (1 row) Time: 99,891 ms postgres=# select 'xxxx' as "Test ss"; Test ss --------- xxxx (1 row) Time: 0,796 ms
Pozor na citlivost identifikátorů!:
postgres=# create table g1(a integer); CREATE TABLE Time: 187,855 ms postgres=# select * from g1; a --- (0 rows) postgres=# select * from G1; a --- (0 rows)
V MySQL:
mysql> create table g1(a integer); Query OK, 0 rows affected (0.04 sec) mysql> select * from g1; Empty set (0.00 sec) mysql> select * from G1; ERROR 1146 (42S02): Table 'foo.G1' doesn't exist
V MySQL nedochází k automatické normalizaci identifikátorů na malá písmena. Proto tam nezpůsobí problém velké písmeno v názvu identifikátoru:
mysql> create table `G3`(a integer); Query OK, 0 rows affected (0.01 sec) mysql> select * from G3; Empty set (0.00 sec)
Pozor, ovšem v PostgreSQL:
postgres=# create table "G3"(a integer);
CREATE TABLE
Time: 20,139 ms
postgres=# select * from G3;
ERROR: relation "g3" does not exist
LINE 1: select * from G3;
^
Dalším rozdílem je case sensitivní porovnávání řetězců v PostgreSQL oproti case insensitive porovnání řetězců v MySQL.
mysql> select 'Ahoj' = 'AHOJ'; +-----------------+ | 'Ahoj' = 'AHOJ' | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> select 'AHOJ' like 'Ah%'; +-------------------+ | 'AHOJ' like 'Ah%' | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec)
PostgreSQL při porovnání řetězců bere ohled na velikost písmen:
postgres=# select 'Ahoj' = 'AHOJ'; ?column? ────────── f (1 row) postgres=# select 'AHOJ' like 'Ah%'; ?column? ────────── f (1 row)
Jiné chování LIKE lze relativně snadno pokrýt operátorem ILIKE
postgres=# select 'AHOJ' ilike 'Ah%'; ?column? ────────── t (1 row)
Pro porovnání řetězců je nutné použít buďto explicitní transformaci na velká (nebo malá) písmena dohromady s funkcionálním indexem (aby nedocházelo k opakovaným zbytečným konverzím):
postgres=# create table foo(a varchar);
CREATE TABLE
postgres=# create index on foo((upper(a)));
CREATE INDEX
postgres=# set enable_seqscan to off; -- pro účely testování
SET
postgres=# explain select * from foo where upper(a) = upper('Nějaký text');
QUERY PLAN
────────────────────────────────────────────────────────────────────────────
Bitmap Heap Scan on foo (cost=4.21..14.37 rows=7 width=32)
Recheck Cond: (upper((a)::text) = 'NĚJAKÝ TEXT'::text)
-> Bitmap Index Scan on foo_upper_idx (cost=0.00..4.21 rows=7 width=0)
Index Cond: (upper((a)::text) = 'NĚJAKÝ TEXT'::text)
(4 rows)
Další možností je použít typ citext (case insensitive text) z extenze citext:
postgres=# create extension citext; -- instalace do aktuální databáze (zde db postgres)
CREATE EXTENSION
postgres=# create table foo2(a citext);
CREATE TABLE
postgres=# insert into foo2 values('Pavel');
INSERT 0 1
postgres=# select * from foo2 where a = 'pavel';
a
───────
Pavel
(1 row)
postgres=# select * from foo2 where a like 'pa%';
a
───────
Pavel
(1 row)
postgres=# create index on foo2(a);
CREATE INDEX
postgres=# set enable_seqscan to off; -- pouze pro testovací účely penalizace seq scan
SET
postgres=# explain select * from foo2 where a like 'pa%';
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────
Index Only Scan using foo2_a_idx on foo2 (cost=0.12..8.14 rows=1 width=32)
Filter: (a ~~ 'pa%'::citext)
(2 rows)
postgres=# explain select * from foo2 where a = 'pavel';
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────
Index Only Scan using foo2_a_idx on foo2 (cost=0.12..8.14 rows=1 width=32)
Index Cond: (a = 'pavel'::citext)
(2 rows)
GROUP BY
V PostgreSQL povinně musí být zobrazovaný sloupec buďto v agregační funkci nebo v seznamu sloupců v klauzuli GROUP BY. V MySQL toto není podmínkou:
postgres=# select * from zamestnanec;
id | jmeno | prijmeni
----+-------+----------
1 | Pavel | Stehule
(1 row)
Time: 1,287 ms
postgres=# select * from statistika ;
id_zam | typ
--------+-----------------
1 | odpracovany den
1 | odpracovany den
1 | odpracovany den
(3 rows)
V MySQL můžeme napsat následující funkční dotaz:
mysql> select jmeno, prijmeni, count(*)
from zamestnanec
join
statistika
on id = id_zam;
+-------+----------+----------+
| jmeno | prijmeni | count(*) |
+-------+----------+----------+
| Pavel | Stehule | 3 |
+-------+----------+----------+
1 row in set (0.00 sec)
Tentýž dotaz bude ovšem není syntakticky správný v PostrgeSQL:
postgres=# select jmeno, prijmeni, count(*) from zamestnanec join statistika on id = id_zam; ERROR: column "zamestnanec.jmeno" must appear in the GROUP BY clause or be used in an aggregate function
Musí se upravit - např. s využitím derivované tabulky:
postgres=# select jmeno, prijmeni, count
from zamestnanec
join
(select id_zam, count(*)
from statistika
group by id_zam) x
on id = id_zam;
jmeno | prijmeni | count
-------+----------+-------
Pavel | Stehule | 3
(1 row)
Další rozdíly mezi PostgreSQL a MySQL
Rozdíly mezi číselnými datovými typy
PostgreSQL nabízí výrazně více datových typů (včetně možnosti instalace vlastních z extenzí) než MySQL, nicméně nepodporuje unsigned integer (beze znaménkové celočíselné) typy, které jsou naopak v MySQL. To u některých aplikací (které např. používaly unsigned typy pro serializaci (uložení) specifických binárních hodnot) působí problémy. Vývojář má několik možností, z nichž každá znamená buďto práci navíc nebo větší obsazenost diskového prostoru. První možností je použít datový typ s větším rozsahem, který ovšem zabírá více místa na disku (u celočíselných typů je to dvojnásobek). Další možností je použít existující extenzi http://pgxn.org/dist/varint nebo si napsat extenzi vlastní. Konečně další možností je použít pro ukládání hodnot pole (agregace např po minutě, hodině) s větším datovým typem a využít skutečnosti, že se pole (cca nad 2KB komprimují).
Pozn: PostgreSQL používá jiný způsob ukládání dat na disku, který je optimalizován primárně pro výkon v multiuživatelském prostředí. Každý řádek v PostgreSQL obsahuje, kromě vlastních dat, 27 bytes systémových dat (http://www.postgresql.org/docs/9.1/static/storage-page-layout.html - Table 55-4. HeapTupleHeaderData Layout), které v některých specifických aplikacích mohou představovat nezanedbatelnou režii vůči MySQL a MyISAM engine. Například pro ukládání časových řad, což je obvykle posloupnost dvojic čísel, se používají několika málo sloupcové tabulky. U takových to tabulek má hlavička řádku v PostgreSQL největší režii - a proto se doporučuje takováto data napřed preagregovat do polí (např. po hodině, dnu, ...) a pak teprve uložit. Výhodou je výrázná redukce systémových dat a velká šance na transparentní komprimaci obsahu pole.
Dalším rozdílem mezi PostgreSQL a MySQL je komplexnější (a komplikovanější) typový systém v PostgreSQL. Důsledkem je ale skutečnost, že jednoznačný výraz zapsaný v MySQL nemusí být jednoznačným v PostgreSQL a je nutné upřesnit zápis přetypováním - navíc se PostgreSQL navržen jako striktně typový systém a některá konverze typů nejsou implicitně povolené (a v MySQL ano), a je nutné si konverzi vynutit přetypováním (různé chování vychází z odlišné filozofie - v PostgreSQL se používají principy, které vedou k předcházení prográmátorských chyb, za cenu větší "buzerace" programátora. V MySQL se naopak povolí vše, co je možné, i za cenu vyššího rizika zanesení chyb):
--Číslo zadané jako řetězecn na MySQL projde na PostgreSQL ne:
SELECT 1+1 AS val;
-> MySql: 2 PostgreSql: 2
SELECT 1+'1' AS val;
-> MySql: 2 PostgreSql: chyba
SELECT 1+CAST('1' AS INT) AS val;
-> MySql: 2 PostgreSql: 2
--MySql automaticky použije vyší datový typ, PostgreSql ne a zahlásí chybu:
SELECT 2147483647+2147483647 AS val;
-> MySql: 4294967294 PostgreSql: chyba (out of range)
SELECT CAST(2147483647 AS BIGINT)+2147483647 AS val;
-> MySql: 4294967294 PostgreSql: 4294967294
--Stejný případ, ale zde vidíme jak je MySql trochu nevyzpytatelné:
SELECT 9223372036854775807+9223372036854775807 AS val;
-> MySql: -2 Chybně!!! PostgreSql: chyba (out of range)
SELECT CAST(9223372036854775807 AS DECIMAL(30,0))+9223372036854775807 AS val;
-> MySql: 18446744073709551614 PostgreSql: 18446744073709551614
--Přirozeně tyto rozdíly fungují stejně pokud vytvoříme tabulku a pracuje nad daty z tabulky:
CREATE TABLE t ( val1 INT, val2 INT, PRIMARY KEY (val1) );
INSERT INTO t (val1, val2) VALUES (2147483647,2147483647);
SELECT val1+val2 AS val FROM t;
-> MySql: 4294967294 PostgreSql: chyba (out of range)
SELECT CAST(val1 AS DECIMAL(30,0))+val2 AS val FROM t;
-> MySql: 4294967294 PostgreSql: 4294967294
--MySql opět trochu nevyzpytatelné:
CREATE TABLE t ( val1 BIGINT, val2 BIGINT, PRIMARY KEY (val1) );
INSERT INTO t (val1, val2) VALUES (9223372036854775807,9223372036854775807);
SELECT val1+val2 AS val FROM t;
-> MySql: -2 Chybně!!! PostgreSql: chyba (out of range)
SELECT CAST(val1 AS DECIMAL(30,0))+val2 AS val FROM t;
-> MySql: 18446744073709551614 Chybně!!! PostgreSql: 18446744073709551614
Návratové typy agregačních funkcí jako například sum() jsou také přesně typové, například SUM() nad INT sloupci vrací výsledek jako BIGINT a SUM() nad BIGINT sloupci vrací výsledek jako NUMERIC. Ale to asi nikoho nepostihne (více než 2^31 záznamů obsahující sloupec INT s hodnotou 2^31 se jen tak neuvidí (za předpokladu, že se nesnažíte např. počítat časy v sec počínaje rokem 1900).
Rozdíly v nastavení práv
Oprávnění jsou aplikována odlišným způsobem. Pro přidělení oprávnění na všechny tabulky v databázi (schematu) ve verzi 9.0 a výše existuje zápis:
GRANT typ-oprávnění ON ALL TABLES IN SCHEMA public TO [GROUP] jméno
tedy například:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO GROUP moje_skupna
nebo
GRANT ALL ON ALL TABLES IN SCHEMA public TO muj_uzivatel
(Obvykle je vhodnější a flexibilnější vytvářet oprávnění na skupiny tedy ne přímo na uživatele i tehdy pokud skupina má jen jednoho uživatele.) více: http://www.postgresql.org/docs/9.0/static/sql-grant.html
Pokud chceme, aby oprávnění bylo automaticky aplikováno i na nově vytvořené tabulky (Obdoba oprávnění definovaného na databázi v MySql):
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP moje_skupina.
Tento příkaz má ale dopad jen na nově vytvářené tabulky.
Pokud máme k dispozici jen osmičkovou verzi, tak oprávnění na všechny tabulky lze definovat jednotlivě
GRANT SELECT ON moje_tabulka SCHEMA public TO GROUP moje_skupna
nebo si vytvořit funkci, která to provede automaticky. Např.:
CREATE OR REPLACE FUNCTION grantOnAllTablesInSchema(uname varchar, sname varchar) RETURNS integer AS $$
DECLARE
r record;
c INT;
BEGIN
c = 0;
FOR r IN SELECT 'GRANT ALL ON ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' to ' || quote_ident(uname) AS sqlstr
FROM pg_tables where schemaname IN (sname) ORDER BY schemaname, tablename
LOOP
EXECUTE r.sqlstr;
c = c + 1;
END LOOP;
RETURN c;
END;
$$ LANGUAGE plpgsql;
Vykonáním funkce získáme počet tabulek v daném schématu, a jako vedlejší efekt dojde k nastavení přístupových práv.
SELECT grantOnAllTablesInSchema('tomas'::varchar,'public'::varchar) AS affected_tables;
V osmičkové řadě není PL/pgSQL defaultně dostupný. Nejprve je nutné jej zaregistrovat pro vybranou databázi:
CREATE LANGUAGE plpgsql;
Ekvivalenty funkcí MySQL, které nejsou v PostgreSQL (MySQL functions for PostgreSQL)
- Expected PostgreSQL 8.4 and higher.
- Vyžaduje PostgreSQL 8.4 a vyšší
Funkce pro práci s řetězci (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;
Místo funkce GROUP_CONCAT použijte agregační funkci 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)
Funkce pro práci s typem datum a čas (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;