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 | ||
---|---|---|
| ||
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 | - | - |
collection | bitstream2collection5 bitstream2collectionhandle5 | bundle2collection5 bundle2collectionhandle5 | item2collection5 | collection2collectionhandle5 collectionhandle2collection5 | - |
community | bitstream2community5 bitstream2communityhandle5 | bundle2community5 bundle2communityhandle5 | item2community5 | collection2community5 collection2communityhandle5 collectionhandle2community5 collectionhandle2communityhandle5 | community2communityhandle5 communityhandle2community5 |
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 | - | - |
collection | ds5_bitstream2collection ds5_bitstream2collectionhandle | ds5_bundle2collection ds5_bundle2collectionhandle | ds5_item2collection | ds5_collection2collectionhandle ds5_collectionhandle2collection | - |
community | ds5_bitstream2community ds5_bitstream2communityhandle | ds5_bundle2community ds5_bundle2communityhandle | ds5_item2community | ds5_collection2community ds5_collection2communityhandle ds5_collectionhandle2community ds5_collectionhandle2communityhandle | ds5_community2communityhandle ds5_communityhandle2community |
Other functions:
ds5_metadata_id2field
ds5_metadata_field2id
Create functions
Code Block | ||
---|---|---|
| ||
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 | ||
| ||
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 | ||
---|---|---|
| ||
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 | ||
---|---|---|
| ||
-- 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 | ||
| ||
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(); |