Life-saving little things

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

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

In any of my databases, I create in the “public” scheme a certain set of such functions that make my life easier. In this topic I want to share them. I invite everyone to share their experiences 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. It is used like this: Do not scold for an example, at two in the morning nothing smarter comes to mind :).

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;


Getting DDL tables

Just what I talked about at the beginning of the topic. 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, like this:

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

Actually there is 1000 and one way to encode / decode url. My method 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, tested and working. Used for example like this:

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

That's it, 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 / comments on my decisions.