SQL Triky III.
Starších dvacet triků Novějších dvacet triků
Pomoc při migraci z TSearch2 na vestavěný fulltext
Ve verzi 8.3 došlo k integraci modulu TSearch2 - bohužel integrovaný fulltext není plně kompatibilní s starším modulem, což působí problémy už při migraci databáze. Pro zjednodušení migraci vývojáři přepsali modul TSearch2 tak, aby maskoval rozdíly mezi integrovaným fulltextem a rozšiřujícím modulem. Modul se musí registrovat do databáze ještě před vlastním načtením dumpu - to v případě importu jednotlivých databází není problém. Problémy nastanou v případě, že dumpujete všechny db naráz. Dump se totiž odkazuje nikoliv na template1, kam můžete TSearch2 předinstalovat, ale na template0, s kterou nelze nijak pracovat. Pokud se setkáte s chybou:
ERROR: type "tsvector" already exists
musíte poeditovat dump (pomůže rozdělit dump na strukturu a data), a přidat za vytvořením databáze import registračního souboru TSearch2.sql:
DROP DATABASE brigady; CREATE DATABASE brigady WITH TEMPLATE = template0 ENCODING = 'UTF8'; -- -- PostgreSQL database dump complete -- \connect brigady \i /usr/local/pgsql/share/contrib/tsearch2.sql SET search_path = public, pg_catalog; ...
Nepodceňujte SQL funkce
Snad jediné pomalé operace, které jsou v PL/pgSQL je změna pole a změna řetězce. V obou případech se totiž nemění pouze jeden prvek (jeden znak), ale generuje se nové pole nebo nový řetězec. Pro malé objekty to není nijak zásadní režie, ale pro velké objekty, a velký počet změn, již může být tato režie znatelná. A proto je dobré se ji vyhnout. V následující ukázce porovnávám rychlost řazení v SQL a v PL/pgSQL (metoda quick sort):
CREATE OR REPLACE FUNCTION quicksort(l integer, r integer, a int[])
RETURNS int[] AS $$
DECLARE akt int[] = a;
i integer := l; j integer := r; x integer = akt[(l+r) / 2];
w integer;
BEGIN
LOOP
WHILE akt[i] < x LOOP i := i + 1; END LOOP;
WHILE x < akt[j] loop j := j - 1; END LOOP;
IF i <= j THEN
w := akt[i];
akt[i] := akt[j]; akt[j] := w;
i := i + 1; j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF l < j THEN akt := quicksort(l,j,akt); END IF;
IF i < r then akt := quicksort(i,r,akt); END IF;
RETURN akt;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
postgres=# SELECT array_upper(quicksort(1,10000,array_agg(a)),1) FROM test;
array_upper
-------------
10000
(1 row)
Time: 5918,531 ms
CREATE OR REPLACE FUNCTION sort(anyarray)
RETURNS anyarray AS $$
SELECT array(SELECT * FROM unnest($1) ORDER BY 1);
$$ language sql;
postgres=# SELECT array_upper(sort(array_agg(a)),1) FROM test;
array_upper
-------------
10000
(1 row)
Time: 35,980 ms
Na výsledku není nic překvapivého - řazení v SQL je implementováno přímo v C, podstatně efektivněji než kdy může být implementováno v PL/pgSQL (v obou případech je ovšem použit quicksort). Pro zajímavost Bublesort během 5min nedoběhl (pro 1000 prvků SQL 7ms, Quick 150ms, Buble 7000ms). Je důležité si stále připomínat, že jednoduchý kód může být velice rychlý, zvlášť pokud je nativně implementován.
Přístup ke změněným datům z statement triggerů
V případě statement triggerů PostgreSQL nenabízí přímo žádnou podporu pro identifikaci řádků, které modifikoval SQL příkaz zodpovědný za aktivaci triggeru. Gurjeet Singh publikoval způsob, jak se v triggeru dotázat na nové hodnoty změněné v aktuální transakci:
CREATE TABLE foo(a integer);
INSERT INTO foo VALUES(0),(0);
CREATE OR REPLACE FUNCTION trghdx()
RETURNS trigger as $$
DECLARE r record;
BEGIN
FOR r IN
SELECT * FROM foo
WHERE xmin::text::bigint = txid_current()
LOOP
RAISE NOTICE '%', r.a;
END LOOP;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg AFTER INSERT OR UPDATE
ON foo FOR EACH STATEMENT EXECUTE PROCEDURE trghdx();
postgres=# INSERT INTO FOO VALUES(10),(10);
NOTICE: 10
NOTICE: 10
INSERT 0 2
postgres=# UPDATE foo SET a = 5 WHERE a = 10;
NOTICE: 5
NOTICE: 5
UPDATE 2
Pozor! V určitých okrajových případech tato metoda není funkční - v každém případě se jedná o nehezký hack.
Rychlý posun indexů na počátek v nule
Tato funkce by se dala ideálně vyřešit v C, kdy stačí nastavit binárně jednu hodnotu ve struktuře popisující pole. PL/pgSQL tuto možnost nenabízí. Iterace po prvku je pomalá. Lze ale využít schopnost SQL parseru rozpoznat rozsah indexů pole:
CREATE OR REPLACE FUNCTION shift_idx(anyarray)
RETURNS anyarray AS $$
DECLARE
mi int := array_upper($1,1);
offset int := array_lower($1,1);
BEGIN
RETURN '[0:' || mi - offset || '] = ' || $1::varchar;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION
postgres=# SELECT shift_idx(array[1,2,3]);
shift_idx
---------------
[0:2]={1,2,3}
(1 row)
Autor: Pavel Stěhule
Rozvinutí hodnot typu record
V případě, že používáme funkce, které vrací record a nemůžeme je umístit do klauzule FROM, musíme použít následující syntax pro rozvinutí hodnoty typu record ve výsledné tabulce:
CREATE FUNCTION foo(OUT a int, OUT b int, IN c int) RETURNS record AS $$ BEGIN a := c + 1; b := c + 2; RETURN; END; $$ LANGUAGE plpgsql; CREATE TABLE t(c int); INSERT INTO t VALUES(10),(20); postgres=# SELECT c, foo(c) FROM t; c | foo ----+--------- 10 | (11,12) 20 | (21,22) (2 rows) postgres=# SELECT c, (foo(c)).* FROM t; c | a | b ----+----+---- 10 | 11 | 12 20 | 21 | 22 (2 rows)
Konverze mezi hexadecimální a dekadickými čísly
Dekadické číslo do hexadecimálního tvaru převedeme velice snadno - PostgreSQL nabízí funkci to_hex. O něco obtížnější je převod v opačném směru - z hexadecimální do dekadické soustavy. PostgreSQL má podporu pro tuto transformaci na úrovni parseru (tj. snadno lze transformovat konstanty), nenabízí ovšem žádnou funkci. Tu si ovšem můžeme poměrně snadno napsat:
CREATE OR REPLACE FUNCTION to_dec(text)
RETURNS integer AS $$
DECLARE r int;
BEGIN
EXECUTE 'SELECT x'||quote_literal($1)|| '::integer' INTO r;
RETURN r;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
postgres=# select to_dec('ff');
to_dec
--------
255
(1 row)
Ještě rychlejší kód bude s použitím jazyka SQL (Michael Glaesemann):
CREATE FUNCTION hex2dec(in_hex TEXT)
RETURNS INT
IMMUTABLE STRICT LANGUAGE sql AS $body$
SELECT CAST(CAST(('x' || CAST($1 AS text)) AS bit(8)) AS INT);
$body$;
Získání defaultních hodnot sloupců tabulky
Jedním z méně častých požadavků je získání výchozích hodnot atributů záznamu. Definice tabulky včetně výchozích hodnot je uložena v systémových tabulek, takže není problém se k těmto hodnotám dostat - jen je s nimi nutné nakládat opatrně - zvlášť s hodnotamy typu timestamp, které se používají k zachycení okamžiku skutečného přidání záznamu do tabulky a nikoliv okamžiku zobrazení formuláře:
CREATE OR REPLACE FUNCTION eval(varchar)
RETURNS varchar AS $$
DECLARE result varchar;
BEGIN
EXECUTE 'SELECT ' || $1 INTO result;
RETURN result;
END;$$ LANGUAGE plpgsql STRICT;
CREATE OR REPLACE FUNCTION defaults(text,
OUT attname name, OUT type varchar, OUT default_val varchar)
RETURNS SETOF RECORD AS $$
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT eval(pg_catalog.pg_get_expr(d.adbin, d.adrelid))
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = $1::regclass::oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
$$ LANGUAGE SQL STRICT;
Číslování řádků po x-té
Opět inspirace z blogu Rolanda Baumana. Při číslování lze použít tzv. session variables (globální proměnné). Ty zatím nejsou implementovány a jejich neexistence se obchází několika způsoby. Takže prvním problémem je jejich implementace, druhým umístění inicializační části. Až v Rolandově blogu jsem se seznámil s trikem, kdy se proměnné inicializují CROSS JOIN poddotazem. Jelikož tento dotaz vrací vždy jeden řádek, není ovlivněn ani výsledek dotazu a ani náročnost dotazu:
create or replace function set_var(varchar, int)
returns int as $$
select set_config($1, $2::text, false)::int
$$ language sql;
create or replace function add_var(varchar, int)
returns int as $$
select set_config($1, (current_setting($1)::int + $2)::text, false)::int
$$ language sql;
postgres=# select add_var('myvar.i', 1) as num, *
from (select length, title, description
from film
cross join
(select set_var('myvar.i', 0)) p --< Inicializace!!!!
order by length ) p
offset 100
limit 10;
num | length | title | description
-----+--------+-----------------------+-------------------------------------------------------------------------------------------------------------------
101 | 60 | PITY BOUND | A Boring Panorama of a Feminist And a Moose who must Defeat a Database Administrator in Nigeria
102 | 60 | ROOM ROMAN | A Awe-Inspiring Panorama of a Composer And a Secret Agent who must Sink a Composer in A Shark Tank
103 | 60 | SHAKESPEARE SADDLE | A Fast-Paced Panorama of a Lumberjack And a Database Administrator who must Defeat a Madman in A MySQL Convention
104 | 60 | SMILE EARRING | A Intrepid Drama of a Teacher And a Butler who must Build a Pastry Chef in Berlin
105 | 61 | BIRDS PERDITION | A Boring Story of a Womanizer And a Pioneer who must Face a Dog in California
106 | 61 | BULWORTH COMMANDMENTS | A Amazing Display of a Mad Cow And a Pioneer who must Redeem a Sumo Wrestler in The Outback
107 | 61 | CAMELOT VACATION | A Touching Character Study of a Woman And a Waitress who must Battle a Pastry Chef in A MySQL Convention
108 | 61 | CASSIDY WYOMING | A Intrepid Drama of a Frisbee And a Hunter who must Kill a Secret Agent in New Orleans
109 | 61 | CHINATOWN GLADIATOR | A Brilliant Panorama of a Technical Writer And a Lumberjack who must Escape a Butler in Ancient India
110 | 61 | DRIFTER COMMANDMENTS | A Epic Reflection of a Womanizer And a Squirrel who must Discover a Husband in A Jet Boat
(10 rows)
Prefix myvar musí být zaregistrován v postgresql.conf.
Výpočet percentilů a mediánu
Určení percentilů a mediánu založeném na funkcích SUBSTRING_INDEX a GROUP_CONCAT je myšlenka Rolanda Boumana. Tato metoda je určitě rychlejší než klasické řešení založené na spojení nebo poddotazů a o něco horší než při použití analytických funkcí. Ovšem MySQL, stejně tak PostgreSQL nemají (červenec 2008) analytické funkce, takže jde o jedno z nejlepších dostupných řešení. PostgreSQL nemá funkci GROUP_CONCAT. Sic by bylo možné použít funkci array_accum, v té ale nemáme možnost určit pořadí. Na druhou stranu v PostgreSQL máme pole, které lze s výhodou využít - neobejdeme se ale bez zákaznických funkcí:
create or replace function nth_percentil(anyarray, int)
returns anyelement as $$
select $1[$2/100.0 * array_upper($1,1) + 1];
$$ language sql immutable strict;
pagila=# select nth_percentil(array(select length from film order by 1),90);
nth_percentil
---------------
173
(1 row)
-- ověření
pagila=# select count(case when length < 173 then 1 end)::float / count(*) * 100.0
from film;
?column?
----------
89.6
(1 row)
Podobný postup lze uplatnit i pro výpočet mediánu:
create or replace function median(anyarray)
returns float as $$
select ($1[array_upper($1,1)/2+1]::double precision + $1[(array_upper($1,1)+1) / 2]::double precision) / 2.0;
$$ language sql immutable strict;
pagila=# select median(array[1,2]), median(array[1,2,3]), median(array[1,2,3,4]);
median | median | median
--------+--------+--------
1.5 | 2 | 2.5
(1 row)
pagila=# select median(array(select length from film order by 1));
median
--------
114
(1 row)
Správa indexů
Na svém blogu zveřejnil Robert Treat několik užitečných dotazů,které mohou pomoci při správě indexů:
-- nepoužité indexy
select indexrelid::regclass as index, relid::regclass as table
from pg_stat_user_indexes
JOIN
pg_index USING (indexrelid)
where idx_scan = 0 and indisunique is false;
-- duplicitní indexy, array_accum z http://www.postgresql.org/docs/8.3/static/xaggr.html
-- POZOR, NEBERE V POTAZ ROZDÍLY MEZI PODMÍNĚNÝMI INDEXY, NEMAZAT ZBRKLE!
select indrelid::regclass, array_accum(indexrelid::regclass)
from pg_index
group by indrelid, indkey
having count(*) > 1;
-- zbytečné indexy
select starelid::regclass, indexrelid::regclass, array_accum(staattnum), relpages, reltuples, array_accum(stadistinct)
from pg_index
join
pg_statistic
on starelid=indrelid and staattnum = ANY(indkey)
join
pg_class
on indexrelid=oid
where case
when stadistinct < 0 then stadistinct > -.8
else reltuples/stadistinct > .2 end
and not (indisunique or indisprimary)
and (relpages > 100 or reltuples > 1000)
group by starelid, indexrelid, relpages, reltuples
order by starelid ;
Před zrušením indexu je dobré se zamyslet - všechny tyto dotazy použijte pro orientaci.
Zrcadlové otočení pole
aneb ukázka nové funkce generate_subscripts (PostgreSQL 8.4):
postgres=# create or replace function array_reverse(anyarray)
returns anyarray as $$
select array(
select $1[i]
from generate_subscripts($1,1,true) g(i))
$$ language sql immutable strict;
CREATE FUNCTION
postgres=# select array_reverse(array[10,20,30]);
array_reverse
---------------
{30,20,10}
(1 row)
Výpočet rozdílu po sobě jdoucích hodnot pomocí korelovaného poddotazu a klauzule LIMIT
Jednou z možností, jak získat rozdíl z po sobě jdoucích hodnot je použití následujícího korelovaného dotazu. Tento způsob výpočtu je vhodný pouze pro malou výslednou množinu (do sta). Pro větší množiny je výhodnější použití SELF JOINu nebo uložené procedury generující tabulku (viz Korelované vnořené dotazy)- Autor: Gurjeet Singh:
ts | size
-------------------+-----
2002-03-16 | 11
2002-03-17 | 16
2002-03-18 | 18
2002-03-19 | 12
select ts, size,
t1.size - (select t2.size
from foo.view as t2
where t2.ts < t1.ts
order by ts desc
limit 1) as diff
from foo.view as t1
order by ts asc;
Překlad modulů na platformě Win32
PostgreSQL je primárně vyvíjen na platformě Linux s překladačem gcc. I to je jeden z důvodů, že vývoj rozšiřujících modulů je komplikovanější na platformě win32 než v systémech typu UNIX. Starší verze jsou přeložené v systému MinGW. Verze 8.3 již může být přeložená v prostředí Microsoft Visual Studio. Základ v úspěchu je ve správném linkování. Pokud je PostgreSQL slinkován s knihovnou MSCVR80.DLL, pak i rozšiřující moduly musí být linkovány proti této knihovně. Dalším problémem je správné určení atributů funkce. Doporučuje se použít makro:
#if defined(_MSC_VER) || defined(__MINGW32__)
#define COPYTEXT_EXPORT __declspec (dllexport)
#else
#define COPYTEXT_EXPORT
#endif
COPYTEXT_EXPORT Datum copytext2(PG_FUNCTION_ARGS) {
// blah blah
}
Použití domény a plperlu pro validaci emailové adresy
Emailovou adresu lze v PostgreSQL kontrolovat poměrně jednoduše pomocí regulárních výrazů. Komplexnější kontrolu umožňuje knihovna Email::Valid. Opět příklad je ukázkou obsáhlosti archivu CPAN. Požití domény je ovšem v tomto případě diskutabilní. V průběhu let se mění mx záznamy - uložené emailové adresy se mohou stát nevalidní (což je na jednu stranu správné, na druhou stranu toto chování může způsobit více problémů než užitku). Osobně bych doporučil používat funkci check_email explicitně, než implicitně. Automatické volání může jednak opravdu značně zpomalit import (kontroluje se validita mx záznamů) a obnovu ze zálohy, dále pak veškeré operace s proměnnými tohoto typu.
CREATE OR REPLACE FUNCTION check_email(varchar)
RETURNS boolean AS $$
use strict;
use Email::Valid;
my $address = $_[0];
my $checks = {
-address => $address,
-mxcheck => 1,
-tldcheck => 1,
-rfc822 => 1,
};
if (defined Email::Valid->address( %$checks )) {
return 'true'
}
elog(WARNING, "address failed $Email::Valid::Details check.");
return 'false';
$$ LANGUAGE plperlu IMMUTABLE STRICT;
postgres=# CREATE DOMAIN email AS varchar CHECK(check_email(value));
CREATE DOMAIN
postgres=# SELECT 'pavel@'::email;
WARNING: address failed rfc822 check.
postgres=# select 'stehule@kix.fsv.cvut.cz'::email;
email
-------------------------
stehule@kix.fsv.cvut.cz
(1 row)
Autor: David Fetter
Akcelerace výběru intervalů splňujících podmínku starti <= x <= endi
Dotazy pro výběr intervalu (interval je určen hodnotami ve sloupcích starti and endi) jsou ve větších tabulkách pomalé. Jádro pudla je v závislosti mezi sloupci starti a endi a dost často i mezi řádky. Řešením je použití prostorových indexů:
postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on testip (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1)
Filter: ((19999999 >= startip) AND (19999999 <= endip))
Total runtime: 434.299 ms
(3 rows)
Time: 435,865 ms
postgres=# CREATE INDEX ggg ON testip USING gist ((box(point(startip,startip),point(endip,endip))) box_ops);
CREATE INDEX
Time: 75530,079 ms
postgres=# EXPLAIN ANALYZE
SELECT *
FROM testip
WHERE box(point(startip,startip),point(endip,endip)) @> box(point (19999999,19999999), point(19999999,19999999));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testip (cost=60.50..2550.14 rows=1000 width=12) (actual time=0.169..0.172 rows=1 loops=1)
Recheck Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
-> Bitmap Index Scan on ggg (cost=0.00..60.25 rows=1000 width=0) (actual time=0.152..0.152 rows=1 loops=1)
Index Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
Total runtime: 0.285 ms
(5 rows)
Time: 2,805 ms
Více na Indexace intervalů - období příp. rozsah ip pomocí prostorových indexů.
Zkrácený zápis pro vložení obsahu proměnných typu row do tabulky
postgres=# CREATE TABLE foo(a integer, b integer);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION fx()
RETURNS void as $$
DECLARE r foo;
BEGIN
SELECT INTO r * FROM foo;
INSERT INTO foo VALUES(r.*);
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT fx();
fx
----
(1 row)
ALTER TABLE ALTER COLUMN USING
Přiznám se, že klauzuli USING v ALTER COLUMN jsem neznal. Žil jsem v domnění, že při změně typu musí být hodnota přetypovatelná na noý typ. Což může být problém např. pro česky zapsané logické konstanty (tj. ano, ne). Pokud bych stávající hodnoty nepřevedl pouhým přetypováním, mohu použít klauzuli USING:
postgres=# CREATE TABLE foo(a varchar);
CREATE TABLE
postgres=# INSERT INTO foo VALUES ('ano');
INSERT 0 1
postgres=# ALTER TABLE foo ALTER COLUMN a TYPE boolean ;
ERROR: column "a" cannot be cast to type "pg_catalog.bool"
postgres=# ALTER TABLE foo
ALTER COLUMN a TYPE boolean
USING CASE a
WHEN 'ano' THEN true
ELSE false END;
ALTER TABLE
postgres=# SELECT * FROM foo;
a
---
t
(1 row)
Quote_ident pro dvojici schéma.název
Vložení identifikátorů mezi uvozovky je jedním ze způsobů ochrany proti SQL injektáži. Funkce qoute_ident projede zadaný řetězec. Pokud zjistí, že řetězec obsahuje nějaký potenciálně nebezpečný řetězec, tak tento řetězec zapouzdří bezpečně do uvozovek, jinak ponechá vstupní řetězec beze změn. Problém nastává ve chvíli, kdy je parametrem kvalifikovaný identifikátor, protože:
postgres=# select quote_ident('public.foo');
quote_ident
--------------
"public.foo"
(1 row)
postgres=# select * from "public.foo";
ERROR: relation "public.foo" does not exist
postgres=# select * from public."foo";
a
---
(0 rows)
postgres=# select * from "public"."foo";
a
---
(0 rows)
Toto omezení funkce quote_ident, lze celkem snadno obejít vlastní funkcí:
CREATE OR REPLACE FUNCTION quote_array(text[])
RETURNS text AS $$
SELECT array_to_string(array(SELECT quote_ident($1[i])
FROM generate_series(1, array_upper($1,1)) g(i)),
'.')
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION quote_schema_ident(text)
RETURNS text AS $$
SELECT quote_array(string_to_array($1,'.'))
$$ LANGUAGE SQL IMMUTABLE;
postgres=# select quote_schema_ident('public.foo tab');
quote_schema_ident
--------------------
public."foo tab"
(1 row)
Autor: Pavel Stěhule.
Rychlé dohledání nejdelšího prefixu
Dohledání nejdelšího prefixu z množiny známých prefixů je poměrně častá úloha. Typicky v oblasti komunikací. Na základě shody prefixu se obvykle určuje tarifní pásmo. Tuto úlohu jsem před několika lety řešil uloženou procedurou a modifikovaným algoritmem pro vyhledávání metodou půlení intervalu. Možná o něco pomalejší, nicméně o dost jednodušší je technika generování prefixů ze zadaného řetězce a přímé dohledání těchto prefixů (autor Hubert Lubaciewski - Depesz).
--tabulka obsahující známé prefixy cca 5000 postgres=# select * from prefixesx limit 5; costcode_name | costcode --------------------+---------- Afghanistan | 93 Afghanistan Mobile | 9370 Afghanistan Mobile | 9379 Afghanistan Mobile | 9380 Alaska (USA) | 1907 (5 rows)
Nejjednodušší a nejhorší řešení je použití LIKE:
postgres=# SELECT *
FROM prefixesx
WHERE '420724181000' LIKE costcode || '%'
ORDER BY length(costcode) DESC
LIMIT 1;
costcode_name | costcode
---------------------------------+----------
Czech Republic Mobile - EuroTel | 42072
Další možností je vygenerovat z každého čísla pole prefixů a tyto prefixy přímo dohledávat:
CREATE OR REPLACE FUNCTION prefixes(varchar)
RETURNS varchar[] AS $$
SELECT ARRAY(SELECT substring($1 FROM 1 FOR i)
FROM generate_series(1, length($1)) g(i))::varchar[];
$$ LANGUAGE sql IMMUTABLE;
a použít dotaz:
postgres=# SELECT *
FROM prefixesx
WHERE costcode = ANY (prefixes('420724191000'))
ORDER BY length(costcode) DESC
LIMIT 1;
costcode_name | costcode
---------------------------------+----------
Czech Republic Mobile - EuroTel | 42072
Na zhruba 5000 řádcích, které představují aktuální ceník, je tento dotaz zhruba 4x rychlejší nežli LIKE (který se bude s rostoucím počtem prefixů zpomalovat).
O něco pomalejší (cca 1ms) je dotaz vynucující si index s LIKE:
SELECT *
FROM prefixesx
WHERE '420724181000' LIKE costcode || '%'
AND costcode LIKE substring('420724191000' FROM 1 FOR 1) || '%'
ORDER BY length(costcode) DESC
LIMIT 1;
Zjištění počtu výskytu subřetězce v řetězci
Následující funkce je ukázkou docela již zlidovělého triku pro operace s řetězci a to použítí funkcí replace a length, přičemž vlastní úlohu řešíme bez cyklu - to je typické pro interprety, kde impementace vestavěných funkcí je rychlejší než iterace:
CREATE OR REPLACE FUNCTION CountInString(text,text) RETURNS integer AS $$ SELECT(Length($1) - Length(REPLACE($1, $2, ''))) / Length($2) ; $$ LANGUAGE SQL IMMUTABLE;
Tuto funkci zaslal Rodrigo E. De León Plicet. Napadlo mne ještě další řešení a to s využítím polí:
CREATE OR REPLACE FUNCTION CountInString(text, text) RETURNS integer AS $$ SELECT Array_upper(String_to_array($1,$2),1) - 1; $$ LANGUAGE SQL IMMUTABLE;