The little things that make life easier

Postgresql, no doubt, a great DBMS. She possesses extensive capabilities, excellent documentation, and is free. However, there will always be something that the user is not to be missed. In postgresql this is easily fixed, because it allows you to create functions in languages for every taste, whether Plpgsql, Perl, or even Java.

Here is an example. I always lacked functions that get the DDL selected table. In oracle, for example, you can use the resources of the dbms_metadata package. But in postgresql counterpart. It is possible of course to use pgdump, but this is a little something I would like to have a database. And so on, I think everyone can find a few of these little "list".

In my every database I create in the schema "public" to a certain set such that to facilitate my life functions. In this topic I want to share them. I invite all to share in the comments your experience.


the

Array to table

With an array of work is not always easy, often there is a desire "to choose from an array SELECTом". In postgresql it is possible.

the 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:

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

Don't scold for example, at two in the morning no brainer doesn't come:).

the

getting the DDL of the table

Exactly what I was talking about in the beginning of the topic.

the CREATE OR REPLACE FUNCTION extract_ddl(IN table_name text, IN db_name text,
IN the 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 greatly reduce the volume by removing the options that you will not use. For example:

the CREATE OR REPLACE FUNCTION extract_ddl(IN table_name text, IN text, db_name)
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';


the

urldecode/urlencode

In fact there is a 1000 and one way to encode/decode the url. My method is only good for a small range of tasks. Some auxiliary query or something like that.

the CREATE OR REPLACE FUNCTION urlencode (url IN text, IN text encoding)
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 (url IN text, IN text encoding)
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;


the

Get domain name from URL

Probably not the best decision, but, nevertheless, tested, and working.

the 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-LOOP 1
v_domain := v_matches[v_url_levels - i] || '.' || v_domain;
END LOOP;
END IF;

RETURN v_domain;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;


Used like this:

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

Result:
-----------------
google.com


the

Fin

All inspiration is now over). Again I urge all to share their experience, which make life easier. I will appreciate any comments/objections to my judgment.

Article based on information from habrahabr.ru