Trivialities that make life easier

Postgresql is without a doubt a great DBMS. It has extensive features, excellent documentation, and with all this is free. However, there is always something that the user will not miss. And in postgresql it is easily fixed, because it allows you to create functions in languages ​​for every taste, be it Plpgsql, Perl, or even Java.



I will give an example. I have always lacked the function that receives the selected table's DDL. In oracle, for example, you can use the dbms_metadata package for this. But for some reason, there is no analogue in postgresql. That is, you can certainly use pgdump, but this is a bit different, I would like to have the function of the database. And so on, I think everyone will find several such small "Wishlist".



In any of my databases, I create in the “public” scheme a specific set of functions that facilitate my life. In this topic I want to share them. I invite everyone to also share their comments in the comments.



')

Array to table

It is not always convenient to work with an array, often there is a desire to “select from an array with a SELECT”. In postgresql this is possible.



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;



Used like this:



SELECT num FROM explode_array('{1,2,3}'::INTEGER[]) num WHERE num = 2;



Do not scold for example, at two o'clock in the morning nothing smarter comes to mind :).

Getting the DDL table

Just what I said at the beginning of the topic.



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';



This is the full version of the function, it can be greatly reduced in volume by removing parameters that you will not use. For example:



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

Actually there is 1000 and one way to encode / decode url. My way is suitable only for a small circle of tasks. Some kind of auxiliary request or something like that.



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;



Get domain name from URL

Surely not the best solution, but, nevertheless, proven and working.



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;





Used for example:



SELECT * FROM extract_domain('http://www.google.com/search?q=postgresql+is+great', 2);



Result:

-----------------

google.com



Fin

That's all, the inspiration for today is over). Again, I urge everyone to share their experiences that make life easier. I will be glad to any comments / remarks to my decisions.