Мелочи, облегчающие жизнь

Postgresql, без сомнения, великолепная СУБД. Она обладает обширнейшими возможностями, отличной документации, и при всем при этом является бесплатной. Однако, всегда найдется что-то, чего пользователю не будет хватать. И в postgresql это легко исправляется, ведь он позволяет создавать функции на языках на любой вкус, будь то Plpgsql, Perl или даже Java.

Приведу пример. Мне всегда не хватало функции, получающей 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

Все, вдохновение на сегодня закончилось). Снова призываю всех делиться своими наработками, облегчающими жизнь. Буду рад любым комментариям/замечаниям к моим решениям.