Using generate_subscripts it is easily possible to reverse a domain like www.example.org to org.example.www using postgres plpgsql.

CREATE OR REPLACE FUNCTION rdomain(name VARCHAR) RETURNS VARCHAR AS $$
DECLARE
	parts VARCHAR[];
BEGIN
	parts = string_to_array(name, '.');
	RETURN array_to_string(
		ARRAY(
			SELECT 
				parts[i]
			FROM 
				generate_subscripts(parts,1,true) AS s(i)
		),
		'.'
	);
END
$$ LANGUAGE plpgsql;

Use like:
SELECT rdomain('www.example.org');
# org.example.www

In order to reverse a domain and remove some segments, e.g. www.example.org → org.example, generate_series can be used:

CREATE OR REPLACE FUNCTION rdomain(name VARCHAR, num int) RETURNS VARCHAR AS $$
DECLARE
	parts VARCHAR[];
BEGIN
	parts = string_to_array(name, '.');
	RETURN array_to_string(
		ARRAY(
			SELECT
				parts[i]
			FROM
				generate_series(
					array_upper(parts,1),
					array_upper(parts,1)-num+1,
					-1
				) AS s(i)
		),
		'.'
	);
END
$$ LANGUAGE plpgsql;

Use like:
SELECT rdomain('www.example.org', 2);
# org.example

Combining both, segments can be removed from a domain, e.g. www.example.org → example.org.

CREATE OR REPLACE FUNCTION basedomain(name VARCHAR, num int) RETURNS VARCHAR AS $$
BEGIN
	RETURN rdomain(rdomain(name, num))
END
$$ LANGUAGE plpgsql;

Use like:
SELECT basedomain('www.example.org', 2);
# example.org

Having a xml field in your postgres database table, you may want to match something with xpath. Unfortunately the result of the match is an ARRAY, and the django.db.backends.postgresql_psycopg2 engine does not convert it properly to a list of ElementTree's.

But, this can be changed:

import xml.etree.ElementTree as ET
from psycopg2.extensions import new_type, register_type
from psycopg2.extensions import STRINGARRAY
 
import psycopg2
def cast_xml_array(value, cur):
	if value is None: 
		return []
	elements = STRINGARRAY(value,cur)
	return [ET.fromstring(i) for i in elements]
 
 
XML = new_type((143,), "XML[]", cast_xml_array)
register_type(XML)

This snippet registers a new type for the OID 143, which is xml[] for me. The result is converted to a list of strings using psycopg2's internal STRINGARRAY type, and this list of strings is converted to a list of ElementTrees.

I just committed xmpp backend code.
The backend code can:

• store files which get streamed to the xmpp service on disk
• store events which get streamed in a postgres database

Read more…