命を救うささいなこと

Postgresqlは間違いなく素晴らしいDBMSです。 豊富な機能、優れたドキュメントを備えており、これらはすべて無料です。 ただし、ユーザーが見逃さないものは常にあります。 また、postgresqlでは、Plpgsql、Perl、またはJavaであっても、あらゆる好みの言語で関数を作成できるため、これは簡単に修正されます。



例を挙げましょう。 選択したテーブルのDDLを取得する機能が常に不足していました。 たとえば、oracleでは、dbms_metadataパッケージを使用してこれを実行できます。 しかし、何らかの理由でpostgresqlには類似物がありません。 つまり、確かにpgdumpを使用できますが、これはすでに少し異なっているため、db関数が必要です。 など、誰もがこのような小さな「ウィッシュリスト」をいくつか見つけると思います。



いずれのデータベースでも、「パブリック」スキームで、生活を楽にする特定の機能セットを作成しています。 このトピックでは、それらを共有したいと思います。 コメントで経験を共有してください。





テーブルへの配列



配列を操作することは必ずしも便利ではありません。多くの場合、「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;







例を挙げてscるな、朝の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をエンコード/デコードする1つの方法があります。 私の方法は、タスクの小さなサークルにのみ適しています。 ある種の補助的なリクエストなど。



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.]*\\.[az]{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









ひれ



これで、今日のインスピレーションは終わりました)。 繰り返しますが、私はすべての人に、人生を楽にする経験を共有することを勧めます。 私の決定に対するコメント/コメントを喜んでいます。



All Articles