Page History

Info
This page is related to DSpace 5. For DSpace 6 see Helper SQL functions for DSpace 6.

If you are making custom SQL queries against the DSpace database, they can get very complex due to numerous joins between various levels of DSpace objects (bitstream, bundle, item, collection, community). These helper functions contain the joins so you don't have to write them every time. In other words, using one of these functions in your query saves you from typing the body of the function in your query.

Naming convention: bitstream2item5ds5_bitstream2item(bitstream_id), returns item id (resource_id), works on DSpace 5.x

...

Code Block

language	sql

SELECT bitstream2item5ds5_bitstream2item(123);  -- 456
 
SELECT (metadatads5_id2field5item2collectionhandle(124)456).element;  -- 'description123456789/789'

SELECT ds5_metadata_field2_id5field2id('dc', 'description', 'abstracttitle');  -- 124

DSpace 5

Overview

argument → result ↓	bitstream	bundle	item	collection	community
bitstream	-	-	-	-	-
bundle	bitstream2bundle5	-	-	-	-
item	bitstream2item5 bitstream2itemhandle5	bundle2item5 bundle2itemhandle5	item2itemhandle5 itemhandle2item5	-	-
collection	bitstream2collection5 bitstream2collectionhandle5	bundle2collection5 bundle2collectionhandle5	item2collection5 item2collectionhandle5 itemhandle2collection5 itemhandle2collectionhandle5	collection2collectionhandle5 collectionhandle2collection5	-
community	bitstream2community5 bitstream2communityhandle5	bundle2community5 bundle2communityhandle5	item2community5 item2communityhandle5 itemhandle2community5 itemhandle2communityhandle5	collection2community5 collection2communityhandle5 collectionhandle2community5 collectionhandle2communityhandle5	community2communityhandle5 communityhandle2community5

Other functions:

metadata_id2field5
metadata_field2_id5

Create functions

64
SELECT ds5_metadata_field2id('dc', 'description', 'abstract');  -- 27
SELECT ds5_metadata_id2field(27);  -- (dc,description,abstract)
SELECT (ds5_metadata_id2field(27)).element;  -- 'description'

Overview

argument → result ↓	bitstream	bundle	item	collection	community
bitstream	-	-	-	-	-
bundle	ds5_bitstream2bundle	-	-	-	-
item	ds5_bitstream2item ds5_bitstream2itemhandle	ds5_bundle2item ds5_bundle2itemhandle	ds5_item2itemhandle ds5_itemhandle2item	-	-
collection	ds5_bitstream2collection ds5_bitstream2collectionhandle	ds5_bundle2collection ds5_bundle2collectionhandle	ds5_item2collection ds5_item2collectionhandle ds5_itemhandle2collection ds5_itemhandle2collectionhandle	ds5_collection2collectionhandle ds5_collectionhandle2collection	-
community	ds5_bitstream2community ds5_bitstream2communityhandle	ds5_bundle2community ds5_bundle2communityhandle	ds5_item2community ds5_item2communityhandle ds5_itemhandle2community ds5_itemhandle2communityhandle	ds5_collection2community ds5_collection2communityhandle ds5_collectionhandle2community ds5_collectionhandle2communityhandle	ds5_community2communityhandle ds5_communityhandle2community

Other functions:

ds5_metadata_id2field
ds5_metadata_field2id

Create functions

Code Block

language	sql

CREATE OR REPLACE FUNCTION ds5_bitstream2bundle(integer) RETURNS integer
AS 'SELECT bundle_id
FROM bundle2bitstream
WHERE bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION ds5_bitstream2item(integer) RETURNS integer
AS 'SELECT item2bundle.item_id
FROM bundle2bitstream, item2bundle
WHERE item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_bitstream2itemhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM bundle2bitstream, item2bundle, handle
WHERE handle.resource_type_id = 2
AND handle.resource_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_bitstream2collection(integer) RETURNS integer
AS 'SELECT collection2item.collection_id
FROM bundle2bitstream, item2bundle, collection2item
WHERE collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_bitstream2collectionhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM bundle2bitstream, item2bundle, collection2item, handle
WHERE handle.resource_type_id = 3
AND handle.resource_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_bitstream2community(integer) RETURNS integer
AS 'SELECT community2collection.community_id
FROM bundle2bitstream, item2bundle, collection2item, community2collection
WHERE community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_bitstream2communityhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM bundle2bitstream, item2bundle, collection2item, community2collection, handle
WHERE handle.resource_type_id = 4
AND handle.resource_id = community2collection.community_id
AND community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

Code Block

language	sql

CREATE OR REPLACE FUNCTION bitstream2bundle5ds5_bundle2item(integer) RETURNS integer
AS 'SELECT bundleitem2bundle.item_id
FROM bundle2bitstreamitem2bundle
WHERE bundle2bitstreamitem2bundle.bitstreambundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
  
CREATE OR REPLACE FUNCTION bitstream2item5ds5_bundle2itemhandle(integer) RETURNS integervarchar
AS 'SELECT handle
FROM item2bundle.item, handle
WHERE handle.resource_type_id
FROM bundle2bitstream,= item2bundle2
WHEREAND item2bundlehandle.bundleresource_id = bundle2bitstreamitem2bundle.bundleitem_id
AND bundle2bitstreamitem2bundle.bitstreambundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bitstream2itemhandle5ds5_bundle2collection(integer) RETURNS varcharinteger
AS 'SELECT handle
FROM bundle2bitstream, item2bundle, handle
WHERE handle.resource_type_id = 2
AND handle.resourcecollection2item.collection_id
FROM item2bundle, collection2item
WHERE collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bitstream2collection5ds5_bundle2collectionhandle(integer) RETURNS integervarchar
AS 'SELECT collection2item.collection_idhandle
FROM bundle2bitstreamitem2bundle, item2bundlecollection2item, collection2itemhandle
WHERE collection2item.itemhandle.resource_type_id = 3
AND handle.resource_id = item2bundlecollection2item.itemcollection_id
AND item2bundlecollection2item.bundleitem_id = bundle2bitstreamitem2bundle.bundleitem_id
AND bundle2bitstreamitem2bundle.bitstreambundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bitstream2collectionhandle5ds5_bundle2community(integer) RETURNS varcharinteger
AS 'SELECT handlecommunity2collection.community_id
FROM bundle2bitstream, item2bundle, collection2item, handlecommunity2collection
WHERE handlecommunity2collection.resourcecollection_type_id = 3
AND handle.resource_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstreamitem2bundle.bundleitem_id
AND bundle2bitstreamitem2bundle.bitstreambundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bitstream2community5ds5_bundle2communityhandle(integer) RETURNS integervarchar
AS 'SELECT community2collection.community_idhandle
FROM bundle2bitstream, item2bundle, collection2item, community2collection, handle
WHERE community2collection.collection handle.resource_type_id = 4
AND handle.resource_id = collection2itemcommunity2collection.collectioncommunity_id
AND collection2itemcommunity2collection.itemcollection_id = item2bundlecollection2item.itemcollection_id
AND item2bundlecollection2item.bundleitem_id = bundle2bitstreamitem2bundle.bundleitem_id
AND bundle2bitstreamitem2bundle.bitstreambundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION bitstream2communityhandle5(integer) RETURNS varchar
AS 'SELECT handle
FROM bundle2bitstream, item2bundle, collection2item, community2collection, handle
WHERE handle.resource_type_id = 4
AND handle.resource_id = community2collection.community_id
AND community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream FUNCTION ds5_item2itemhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM handle
WHERE handle.resource_type_id = 2
AND handle.resource_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

 
CREATE OR REPLACE FUNCTION bundle2item5ds5_itemhandle2item(integervarchar) RETURNS integer
AS 'SELECT item2bundle.itemresource_id
  FROM item2bundlehandle
  WHERE item2bundlehandle.bundleresource_type_id = 2
  AND handle = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
  
CREATE OR REPLACE FUNCTION bundle2itemhandle5ds5_item2collection(integer) RETURNS varchar
AS 'SELECT handle
FROM item2bundle, handle
WHERE handle.resource_type_id = 2
AND handle.resource_id = item2bundle.item_id
AND item2bundle.bundleinteger
AS 'SELECT collection2item.collection_id
FROM collection2item
WHERE collection2item.item_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bundle2collection5ds5_item2collectionhandle(integer) RETURNS integervarchar
AS 'SELECT handle
FROM collection2item.collection, handle
WHERE handle.resource_type_id
FROM item2bundle,= collection2item3
WHEREAND collection2itemhandle.itemresource_id = item2bundlecollection2item.itemcollection_id
AND item2bundlecollection2item.bundleitem_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bundle2collectionhandle5ds5_itemhandle2collection(integervarchar) RETURNS varcharinteger
AS 'SELECT handlecollection_id
FROM item2bundle, collection2item, handle
WHERE handlecollection2item.resource_typeitem_id = 3(
AND  SELECT handle.resource_id
  = collection2item.collection_id
AND collection2item.itemFROM handle
  WHERE handle.resource_type_id = item2bundle.item_id
AND item2bundle.bundle_id2
  AND handle = $1$1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION bundle2community5ds5_itemhandle2collectionhandle(integervarchar) RETURNS integervarchar
AS 'SELECT community2collection.community_idhandle
FROM item2bundle, collection2item, community2collectionhandle
WHERE community2collection.collection handle.resource_type_id = 3
AND handle.resource_id = collection2item.collection_id
AND collection2item.itemAND collection2item.item_id = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = item2bundle.item_id
AND item2bundle.bundle_id2
  AND handle = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION bundle2communityhandle5ds5_item2community(integer) RETURNS varcharinteger
AS 'SELECT handlecommunity_id
FROM item2bundle, collection2item, community2collection, handle
WHERE handle.resource_type_id = 4
AND handle.resource_id = community2collection.community_id
ANDWHERE community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = item2bundle.item_id
AND item2bundle.bundle_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
  
CREATE OR REPLACE FUNCTION item2itemhandle5ds5_item2communityhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM collection2item, community2collection, handle
WHERE handle.resource_type_handle.resource_type_id = 4
AND handle.resource_id = community2collection.community_id
AND community2collection.collection_id = 2collection2item.collection_id
AND handlecollection2item.resourceitem_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
 
CREATE OR REPLACE FUNCTION itemhandle2item5ds5_itemhandle2community(varchar) RETURNS integer
AS '
AS 'SELECT community_id
FROM collection2item, community2collection
WHERE community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = 2
  AND handle = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION item2collection5(integer) RETURNS integer
AS 'SELECT collection2item.collection_id
FROM collection2item
WHERE collection2item.item_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION item2collectionhandle5ds5_itemhandle2communityhandle(integervarchar) RETURNS varchar
AS 'SELECT handle
FROM collection2item, community2collection, handle
WHERE handle.resource_type_id = 34
AND handle.resource_id = collection2itemcommunity2collection.collectioncommunity_id
AND collection2itemcommunity2collection.itemcollection_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION itemhandle2collection5(varchar) RETURNS integer
AS 'SELECT collection2item.collection_id
FROMAND collection2item
WHERE collection2item.item_id = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = 2
  AND handle = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION itemhandle2collectionhandle5(varchar) RETURNS varchar
AS 'SELECT handle
FROM collection2item, handle
WHERE handle.resource_type_id = 3
AND handle.resource_id = collection2item.collection_id
AND collection2item.item_id = (
  SELECT resource_id
  FROM handle
  ds5_collection2collectionhandle(integer) RETURNS varchar
AS 'SELECT handle
FROM handle
WHERE handle.resource_type_id = 23
  AND handle.resource_id = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION item2community5ds5_collectionhandle2collection(integervarchar) RETURNS integer
AS 'SELECT communityresource_id
  FROM collection2item, community2collection
handle
  WHERE community2collectionhandle.collectionresource_type_id = collection2item.collection_id
AND collection2item.item_id3
  AND handle = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
  
CREATE OR REPLACE FUNCTION item2communityhandle5ds5_collection2community(integer) RETURNS varcharinteger
AS 'SELECT handle
FROM collection2item, community2collection, handle
WHERE handle.resource_type_id = 4
AND handle.resource_id = community2collection.community_id
AND community2collection.collection_id = collection2item.collection community_id
ANDFROM collection2item.item, community2collection
WHERE community2collection.collection_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION itemhandle2community5ds5_collection2communityhandle(varcharinteger) RETURNS integervarchar
AS 'SELECT community_idhandle
FROM collection2item, community2collection
WHERE community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = (
  SELECT resource_id
  FROM, handle
  WHERE handle.resource_type_id = 24
AND handle.resource_id = community2collection.community_id
AND handlecommunity2collection.collection_id = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION itemhandle2communityhandle5ds5_collectionhandle2community(varchar) RETURNS varcharinteger
AS 'SELECT handlecommunity_id
FROM collection2item, community2collection, handle

WHERE community2collection.collection_id = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = 43
  AND handle.resource_id = community2collection.community_id
AND community2collection.collection_id = collection2item.collection_id
AND collection2item.item_id = (
  SELECT resource_id
  FROM handle
  $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION ds5_collectionhandle2communityhandle(varchar) RETURNS varchar
AS 'SELECT handle
FROM collection2item, community2collection, handle
WHERE handle.resource_type_id = 24
  AND handle.resource_id = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION collection2collectionhandle5(integer) RETURNS varchar
AS 'SELECT handle
FROM handle
community2collection.community_id
AND community2collection.collection_id  = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = 3
  AND handle.resource_id = $1
)'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION collectionhandle2collection5ds5_community2communityhandle(varcharinteger) RETURNS integervarchar
AS 'SELECT resource_idhandle
  FROM handle
  WHERE handle.resource_type_id = 34
  AND handle.resource_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION collection2community5ds5_communityhandle2community(integervarchar) RETURNS integer
AS 'SELECT communityresource_id
  FROM handle
 collection2item, community2collection
WHERE community2collectionhandle.collectionresource_type_id = 4
  AND handle = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
  
CREATE OR REPLACE FUNCTION collection2communityhandle5ds5_metadata_id2field(integer) RETURNS TABLE (schema varchar, element varchar, qualifier varchar)
AS 'SELECT handle
FROM collection2item short_id AS schema, community2collectionelement, handle
WHERE handle.resource_type_id = 4
AND handle.resourcequalifier
FROM metadatafieldregistry, metadataschemaregistry
WHERE metadatafieldregistry.metadata_schema_id = community2collectionmetadataschemaregistry.communitymetadata_schema_id
AND community2collection.collectionmetadata_field_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION collectionhandle2community5ds5_metadata_field2id(varchar, varchar, varchar) RETURNS integer
AS 'SELECT community_id
FROM collection2item, community2collection
WHERE community2collection.collection_id = (
  SELECT resource
AS 'SELECT metadata_field_id
FROM metadatafieldregistry, FROM handlemetadataschemaregistry
WHERE  WHERE handle.resource_typemetadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id
AND short_id = 3$1
AND element = $2
AND handlequalifier = $1
)$3'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION collectionhandle2communityhandle5(ds5_metadata_field2id(varchar, varchar) RETURNS varcharinteger
AS 'SELECT handle metadata_field_id
FROM collection2itemmetadatafieldregistry, community2collection, handlemetadataschemaregistry
WHERE handlemetadatafieldregistry.resourcemetadata_typeschema_id = 4
AND handle.resource_id = community2collection.communitymetadataschemaregistry.metadata_schema_id
AND community2collection.collection_id  = (
  SELECT resource_id
  FROM handle
  WHERE handle.resource_type_short_id = 3$1
AND element = $2
AND handlequalifier =IS $1
)NULL'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

Drop functions

Code Block

language	sql

DROP FUNCTION ds5_bitstream2bundle(integer);

CREATE OR REPLACEDROP FUNCTION ds5_bitstream2item(integer);
DROP FUNCTION ds5_bitstream2itemhandle(integer);
DROP FUNCTION community2communityhandle5ds5_bitstream2collection(integer) RETURNS varchar
AS 'SELECT handle
FROM handle
WHERE handle.resource_type_id = 4
AND handle.resource_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION communityhandle2community5(varchar) RETURNS integer
AS 'SELECT resource_id
  FROM handle
  WHERE handle.resource_type_id = 4
  AND handle = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;
 
CREATE OR REPLACE FUNCTION metadata_id2field5(integer) RETURNS TABLE (schema varchar, element varchar, qualifier varchar)
AS 'SELECT short_id AS schema, element, qualifier
FROM metadatafieldregistry, metadataschemaregistry
WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id
AND metadata_field_id = $1'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION metadata_field2_id5;
DROP FUNCTION ds5_bitstream2collectionhandle(integer);
DROP FUNCTION ds5_bitstream2community(integer);
DROP FUNCTION ds5_bitstream2communityhandle(integer);
DROP FUNCTION ds5_bundle2item(integer);
DROP FUNCTION ds5_bundle2itemhandle(integer);
DROP FUNCTION ds5_bundle2collection(integer);
DROP FUNCTION ds5_bundle2collectionhandle(integer);
DROP FUNCTION ds5_bundle2community(integer);
DROP FUNCTION ds5_bundle2communityhandle(integer);
DROP FUNCTION ds5_item2itemhandle(integer);
DROP FUNCTION ds5_itemhandle2item(varchar);
DROP FUNCTION ds5_item2collection(integer);
DROP FUNCTION ds5_item2collectionhandle(integer);
DROP FUNCTION ds5_itemhandle2collection(varchar);
DROP FUNCTION ds5_itemhandle2collectionhandle(varchar);
DROP FUNCTION ds5_item2community(integer);
DROP FUNCTION ds5_item2communityhandle(integer);
DROP FUNCTION ds5_itemhandle2community(varchar);
DROP FUNCTION ds5_itemhandle2communityhandle(varchar);
DROP FUNCTION ds5_collection2collectionhandle(integer);
DROP FUNCTION ds5_collectionhandle2collection(varchar);
DROP FUNCTION ds5_collection2community(integer);
DROP FUNCTION ds5_collection2communityhandle(integer);
DROP FUNCTION ds5_collectionhandle2community(varchar);
DROP FUNCTION ds5_collectionhandle2communityhandle(varchar);
DROP FUNCTION ds5_community2communityhandle(integer);
DROP FUNCTION ds5_communityhandle2community(varchar);
DROP FUNCTION ds5_metadata_id2field(integer);
DROP FUNCTION ds5_metadata_field2id(varchar, varchar, varchar) RETURNS integer
AS 'SELECT metadata_field_id
FROM metadatafieldregistry, metadataschemaregistry
WHERE metadatafieldregistry.metadata_schema_id = metadataschemaregistry.metadata_schema_id
AND short_id = $1
AND element = $2
AND qualifier = $3'
LANGUAGE SQL  STABLE  RETURNS NULL ON NULL INPUT;

Drop functions

;
DROP FUNCTION ds5_metadata_field2id(varchar, varchar);

Functions modifying data

Code Block

language	sql

-- returns eperson_id or NULL if no such eperson was found
CREATE OR REPLACE FUNCTION ds5_eperson_delete_by_eperson_id(int) RETURNS int
AS $$
BEGIN
  DELETE FROM metadatavalue WHERE resource_type_id = 7 AND resource_id = $1;
  DELETE FROM subscription WHERE eperson_id = $1;
  DELETE FROM epersongroup2eperson WHERE eperson_id = $1;
  DELETE FROM eperson WHERE eperson_id = $1;
  RETURN $1;
END
$$ LANGUAGE plpgsql;

-- returns eperson_id or NULL if no such eperson was found
CREATE OR REPLACE FUNCTION ds5_eperson_delete_by_email(character varying) RETURNS int
AS $$
#print_strict_params on
BEGIN
  RETURN ds5_eperson_delete_by_eperson_id((SELECT eperson_id FROM eperson WHERE email = $1));
END
$$ LANGUAGE plpgsql;


SELECT ds5_eperson_delete_by_email('johndoe@example.com');


DROP FUNCTION ds5_eperson_delete_by_eperson_id(int);
DROP FUNCTION ds5_eperson_delete_by_email(character varying

Code Block

language	sql

DROP FUNCTION bitstream2bundle5();
DROP FUNCTION bitstream2item5();
DROP FUNCTION bitstream2itemhandle5();
DROP FUNCTION bitstream2collection5();
DROP FUNCTION bitstream2collectionhandle5();
DROP FUNCTION bitstream2community5();
DROP FUNCTION bitstream2communityhandle5();
DROP FUNCTION bundle2item5();
DROP FUNCTION bundle2itemhandle5();
DROP FUNCTION bundle2collection5();
DROP FUNCTION bundle2collectionhandle5();
DROP FUNCTION bundle2community5();
DROP FUNCTION bundle2communityhandle5();
DROP FUNCTION item2itemhandle5();
DROP FUNCTION itemhandle2item5();
DROP FUNCTION item2collection5();
DROP FUNCTION item2collectionhandle5();
DROP FUNCTION itemhandle2collection5();
DROP FUNCTION itemhandle2collectionhandle5();
DROP FUNCTION item2community5();
DROP FUNCTION item2communityhandle5();
DROP FUNCTION itemhandle2community5();
DROP FUNCTION itemhandle2communityhandle5();
DROP FUNCTION collection2collectionhandle5();
DROP FUNCTION collectionhandle2collection5();
DROP FUNCTION collection2community5();
DROP FUNCTION collection2communityhandle5();
DROP FUNCTION collectionhandle2community5();
DROP FUNCTION collectionhandle2communityhandle5();
DROP FUNCTION community2communityhandle5();
DROP FUNCTION communityhandle2community5();
DROP FUNCTION metadata_id2field5();
DROP FUNCTION metadata_field2_id5();

Page tree

Versions Compared

Old Version 5

New Version Current

Key

DSpace 5

Overview

Create functions

Overview

Create functions

Drop functions

Drop functions

Functions modifying data