Приведу пример. Мне всегда не хватало функции, получающей DDL выбранной таблицы. В oracle, например, вы можете воспользоваться для этого средствами пакета dbms_metadata. А вот в postgresql аналога почему-то нет. То есть можно конечно использовать pgdump, но это уже немного не то, мне хотелось бы иметь функцию бд. И так далее, думаю у каждого найдется несколько таких небольших «хотелок».
В любой моей базе я создаю в схеме «public» определенный набор вот таких облегчающих мне жизнь функций. В этом топике я хочу поделиться ими. Приглашаю всех также поделиться в комментариях своими наработками.
Array to table
С массивом работать не всегда удобно, часто возникает желание «выбрать из массива SELECTом». В postgresql это возможно.
CREATE OR REPLACE FUNCTION explode_array(IN in_array anyarray)
RETURNS SETOF anyelement AS
$$
SELECT ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$ LANGUAGE `sql` IMMUTABLE;
Используется примерно так:
SELECT num FROM explode_array(`{1,2,3}`::INTEGER[]) num WHERE num = 2;
Не ругайте за пример, в два часа ночи ничего умнее в голову не приходит:).
Получение DDL таблицы
Как раз то, о чем я говорил в начале топика.
CREATE OR REPLACE FUNCTION extract_ddl(IN table_name text, IN db_name text,
IN host text, IN user_name text)
RETURNS text AS
$$
my $table_name = $_[0];
my $db_name = $_[1];
my $host = $_[2];
my $user_name = $_[3];
my $str = `pg_dump -s -t $table_name -h $host -U $user_name $db_name`;
return $str;
$$ LANGUAGE `plperlu`;
Это полная версия функции, ее можно сильно уменьшить в объеме, убрав параметры, которые вы не будете использовать. Например так:
CREATE OR REPLACE FUNCTION extract_ddl(IN table_name text, IN db_name text)
RETURNS text AS
$$
my $table_name = $_[0];
my $db_name = $_[1];
my $str = `pg_dump -s -t $table_name $db_name`;
return $str;
$$ LANGUAGE `plperlu`;
urldecode/urlencode
На самом деле существует 1000 и один способ кодировать/раскодировать url. Мой способ годится только для небольшого круга задач. Какой-то вспомогательный запрос или что-то подобное.
CREATE OR REPLACE FUNCTION urlencode (IN url text, IN encoding text)
RETURNS text AS
$$
use URI::Escape;
use Encode;
my $url=$_[0];
my $encoding=$_[1];
return uri_escape(encode($encoding, $url));
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION urldecode (IN url text, IN encoding text)
RETURNS text AS
$$
use Encode;
use URI::Escape;
my $str = uri_unescape($_[0]);
my $encoding = $_[1];
eval {
$str = decode($encoding, $str);
};
if ($@){
return $str;
};
return $str;
$$ LANGUAGE plperlu IMMUTABLE;
Получить доменное имя из URL
Наверняка далеко не лучшее решение, но, тем не менее, проверенное и работающее.
CREATE OR REPLACE FUNCTION extract_domain(IN url text, IN domain_level INTEGER)
RETURNS text AS
$$
DECLARE
v_domain_full text;
v_domain text;
v_matches text[];
v_level INTEGER := 1;
v_url_levels INTEGER := 0;
rec record;
BEGIN
SELECT regexp_matches(lower(url), E`https?://(www\\.)?([-a-zA-Z0-9.]*\\.[a-z]{2,5})`, `gi`) INTO v_matches LIMIT 1;
IF v_matches IS NULL OR v_matches[2] IS NULL THEN
RETURN NULL;
END IF;
v_domain_full := v_matches[2];
v_matches := regexp_split_to_array(v_domain_full, E`\\.`);
SELECT count(*) INTO v_url_levels FROM regexp_split_to_table(v_domain_full, E`\\.`);
IF v_url_levels = domain_level THEN
RETURN v_domain_full;
END IF;
IF v_url_levels < domain_level THEN
RETURN NULL;
END IF;
v_domain := v_matches[v_url_levels];
IF (domain_level > 1) THEN
FOR i IN 1..domain_level-1 LOOP
v_domain := v_matches[v_url_levels - i] || `.` || v_domain;
END LOOP;
END IF;
RETURN v_domain;
END;
$$ LANGUAGE `plpgsql` IMMUTABLE;
Используется например так:
SELECT * FROM extract_domain(`http://www.google.com/search?q=postgresql+is+great`, 2);
Result:
-----------------
google.com
Fin
Все, вдохновение на сегодня закончилось). Снова призываю всех делиться своими наработками, облегчающими жизнь. Буду рад любым комментариям/замечаниям к моим решениям.