Timer function for PostgreSQL

This function will return execution time for any SELECT statement given as a parameter. It is useful for queries in psql.

DROP FUNCTION IF EXISTS timer(VARCHAR);
CREATE FUNCTION timer(VARCHAR)
RETURNS INTERVAL AS'

DECLARE
t1 TIMESTAMP;
t2 TIMESTAMP;

BEGIN

t1 = timeofday();
EXECUTE $1;
t2 = timeofday();

RETURN (t2 - t1);
END;'
language 'plpgsql';


EXAMPLE:
=> SELECT timer('SELECT foo FROM bar LIMIT 100');

-[ RECORD 1 ]----------
timer | 00:00:00.012927


Convert integers to base36 in PostgreSQL

This is PostgreSQL function for converting integers to base36 numbering system:

CREATE OR REPLACE FUNCTION dec2base36(bigint)
RETURNS character varying AS'

DECLARE
inparam alias FOR $1;
invalue bigint;
CLIST char ARRAY[35] :='{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,
F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}';
rv varchar :=''; -- rv = resulting value

BEGIN
invalue = inparam;

WHILE invalue != 0 LOOP
rv = (CLIST[((invalue % 36)+1)]) || rv;
invalue = (invalue/36);
END LOOP;

RETURN(rv);
END;'
language 'plpgsql';


EXAMPLE:
=> SELECT dec2base36(123456789);

-[ RECORD 1 ]------
dec2base36 | 21I3V9


PostGIS špikeri

Konvertēt MULTIPOLYGON uz POLYGON ģeometrijas veidu:
CREATE TABLE gislatvija.mezi_simple AS SELECT gid, shape_leng, shape_area, (public.st_dump(the_geom)).geom AS the_geom FROM gislatvija.mezi_multi;

Validēt ģeometriju:
SELECT st_isvalid(the_geom) FROM foo WHERE gid = 40;
st_isvalid
------------
t
(1 row)

Uzzināt objekta ģeometrijas izmēru megabaitos:
SELECT st_mem_size(the_geom)/131072 as size_in_mb FROM foo WHERE gid = 40;
size_in_mb
---------
2
(1 row)


Ģenerēt 'bounding box' ģeometriju no koordinātēm:
UPDATE mytable SET the_geom = st_geomfromtext('POLYGON((' || minx || ' ' || maxy || ',' ||
maxx || ' ' || maxy || ',' || maxx || ' ' || miny || ',' || minx || ' ' || miny || ',' || minx ||
' ' || maxy || '))', 27700);

Shapefile dump from PostGIS database:
pgsql2shp -u lasma -h localhost -f myfile mydatabase "select id, ('/home/lasma/' || tif_path) as location from myschema.mytable where name = 'Worcester'"