Add an entity

CREATE OR REPLACE FUNCTION schema.entity_add(prm_token integer, prm_name text)
RETURNS integer
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  ret integer;
BEGIN
  PERFORM login._token_assert(prm_token, '{entity_right}');
  INSERT INTO schema.entity (ent_name) VALUES (prm_name)
    RETURNING ent_id INTO ret;
  RETURN ret;
END;
$$;
COMMENT ON FUNCTION schema.entity_add(prm_token integer, prm_name text) 
IS 'Add a new entity';

Delete an entity

CREATE OR REPLACE FUNCTION schema.entity_delete(prm_token integer, prm_id integer)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
  PERFORM login._token_assert(prm_token, '{entity_right}');
  DELETE FROM schema.entity WHERE ent_id = prm_id;
  -- Raise an exception if entity did not exist
  IF NOT FOUND THEN
    RAISE EXCEPTION USING ERRCODE = 'no_data_found';
  END IF;
END;
$$;
COMMENT ON FUNCTION schema.entity_delete(prm_token integer, prm_id integer) 
IS 'Delete a given entity';

Set relations to an entity

Set a list of relations to a given entity. Should:

• remove relations present in the database not present in the new list
• add relations from the list not yet present in the database
• make the minimum of editions

CREATE OR REPLACE FUNCTION schemacs.entity_set_relations(
  prm_token integer, 
  prm_id integer, 
  prm_relations integer[]
)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  t integer;
BEGIN
  PERFORM login._token_assert(prm_token, '{entity_right}');
  -- Raise an exception if entity does not exist
  IF NOT EXISTS (SELECT 1 FROM schema.entity WHERE ent_id = prm_id) THEN
    RAISE EXCEPTION USING ERRCODE = 'no_data_found';
  END IF;
  -- If list is NULL, remove all relations
  IF prm_relations ISNULL THEN
    DELETE FROM schema.entity_relation WHERE ent_id = prm_id;
    RETURN;
  END IF;
  -- Delete relations present in DB not present in list
  DELETE FROM schema.entity_relation WHERE ent_id = prm_id AND rel_id <> ALL(prm_relations);
  -- Add relations in list not yet in DB
  FOREACH t IN ARRAY prm_relations
  LOOP
    IF NOT EXISTS (SELECT 1 FROM schema.entity_relation WHERE ent_id = prm_id AND rel_id = t) THEN
      INSERT INTO schema.entity_relation (ent_id, rel_id) VALUES (prm_id, t);
    END IF;
  END LOOP;
END;
$$;
COMMENT ON FUNCTION schema.entity_set_relations(prm_token integer, prm_id integer, prm_relations integer[]) 
IS 'Set the relations of a given entity. A NULL value for the relation list will remove all relations.';