-- Find unique index not null (that is also definition of primary key, so you
-- will automacially get primary keys also) for table.
-- For better solution see
-- http://archives.postgresql.org/pgsql-patches/2003-07/msg00025.php
-- but it's still not in PostgreSQL 7.3.

DROP FUNCTION pgindex_is_notnull(oid,oid);

-- check if all fields in index are not null
CREATE FUNCTION pgindex_is_notnull(oid,oid) RETURNS boolean AS '
DECLARE
	all_notnull boolean;
	row RECORD;
BEGIN
	all_notnull := true;
	FOR row IN select a1.attnotnull from pg_attribute a1, pg_attribute a2 where a1.attrelid = $1 and a2.attrelid=$2 and a1.attname = a2.attname
	LOOP
		IF row.attnotnull THEN
			all_notnull := false;
		END IF;
	END LOOP;
	RETURN all_notnull;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION pgindex_nrcols(oid);

-- count number of columns in index
CREATE FUNCTION pgindex_nrcols(oid) RETURNS int AS '
DECLARE
	row RECORD;
	nr int;
BEGIN
	nr := 0;
	FOR row IN select * from pg_attribute where attrelid = $1
	LOOP
		nr := nr + 1;
	END LOOP;
	RETURN nr;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION pgindex_cols(oid);

-- count number of columns in index
CREATE FUNCTION pgindex_cols(oid) RETURNS text AS '
DECLARE
	row RECORD;
	cols text;
BEGIN
	cols := null;
	FOR row IN select * from pg_attribute where attrelid = $1
	LOOP
		IF cols is null THEN
			cols := row.attname;
		ELSE
			cols := cols || '',''  || row.attname;
		END IF;
	END LOOP;
	RETURN cols;
END;

' LANGUAGE 'plpgsql';
SELECT
	pgindex_is_notnull(i.indexrelid,i.indrelid) as notnull,
	pgindex_nrcols(i.indexrelid) as nr,
	pgindex_cols(i.indexrelid) as cols,
	c.relname as table,c2.relname as index_name,
	pg_catalog.pg_get_indexdef(i.indexrelid) as definition
FROM
	pg_catalog.pg_class c,
	pg_catalog.pg_class c2,
	pg_catalog.pg_index i
WHERE
	c.oid = i.indrelid AND i.indexrelid = c2.oid
	and i.indisunique
	and c.relname not like 'pg_%'
	and c.relname not like '_rserv%'
ORDER BY
	c.relname DESC,i.indisprimary DESC, i.indisunique DESC, c2.relname;