DSpace metadata discovery utilities

DMeta.sql

-- Function to query metadata values and return table, all parameters optional
/* Example usages: 
	SELECT DMeta( 4317 ); -- all metadata for Item 4317 as a single column with results in a set
	SELECT * FROM DMeta( 4317 ); -- all metadata for Item 4317 with results as a table
	SELECT * FROM DMeta(); -- all metadata as a table
	SELECT * FROM DMeta( NULL, 'dc.date.issued' ); -- all metadata using qualified element 'dc.date.issued'
	SELECT "value" FROM DMeta( 4317, 'dc.subject' ); -- all metadata values for Item 4317 using element 'dc.subject'
	SELECT * FROM DMeta( 4317, 'dc.subject', 'corporate' ); -- all metadata records for Item 4317 where 'dc.subject' contains "corporate"
	SELECT * FROM DMeta( NULL, NULL, 'corporate' ); -- all metadata records with value containing "corporate"
*/

CREATE OR REPLACE FUNCTION DMeta(p_id integer DEFAULT NULL, p_name text DEFAULT NULL, p_value text DEFAULT NULL) RETURNS TABLE(id int, "element" text, "value" text) AS $$
DECLARE qualified_form text = '( short_id || ''.'' || element || CASE WHEN qualifier IS NULL THEN '''' ELSE ''.'' || qualifier END )';
DECLARE sql text = 'SELECT item_id, ' || qualified_form || ' AS qualified, text_value
	FROM metadatafieldregistry R, metadatavalue V, metadataschemaregistry S
	WHERE R.metadata_field_id = V.metadata_field_id
	AND R.metadata_schema_id = S.metadata_schema_id
	';
BEGIN
	IF p_id IS NOT NULL THEN
		sql := sql || ' AND item_id = ' || p_id;
	END IF;

	IF p_name IS NOT NULL THEN
		sql := sql || ' AND ' || qualified_form || ' ILIKE ' || quote_literal(p_name); -- hmm, to wildcard or not?
	END IF;

	IF p_value IS NOT NULL THEN
		sql := sql || ' AND text_value ILIKE ' || quote_literal( '%' || p_value || '%' );
	END IF;

	RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE plpgsql;

meta-view.sql

-- View of the same, might be preferable in some contexts with predicates
-- Example usage: SELECT * FROM metadata WHERE id = 4317;

CREATE OR REPLACE VIEW metadata (id, "element", "value") AS
	SELECT item_id, ( short_id || '.' || element || CASE WHEN qualifier IS NULL THEN '' ELSE '.' || qualifier END ), text_value
		FROM metadatafieldregistry R, metadatavalue V, metadataschemaregistry S
		WHERE R.metadata_field_id = V.metadata_field_id
		AND R.metadata_schema_id = S.metadata_schema_id;

This was useful for me, thanks. In current (5.5) DSpace version item_id is resource_id.