CODEWITHSUNDEEP
postgresql
Paul
Paul

Reputation: 1066

Return deleted records from function

I'm trying to return the Id of a deleted record from within a function.

CREATE OR REPLACE FUNCTION delete_person(_person_id UUID)
RETURNS TABLE(id UUID)
AS
$$
BEGIN

    -- delete user first
    DELETE FROM users WHERE user_id = (SELECT user_id FROM persons WHERE person_id = _person_id);

    -- delete person
    RETURN QUERY
    DELETE FROM persons 
        WHERE person_id = _person_id
        RETURNING person_id AS id;

END;

Wrapping inside a CTE doesn't work. I could use RETURN QUERY SELECT _person_id, but if the record does not exists, I'll still get an answer. I could count the number of records deleted, I could use an exists, but that seems all very rudy.

I just can't find the syntax, nor do I understand why this statements fails to return the deleted records (It returns 0 rows - even though the record is deleted).

Upvotes: 0

Views: 317

Answers (1)

Paul
Paul

Reputation: 1066

Since there was a cascade delete placed on users there was actually never a person record deleted. However, if knowing if that delete on users was successful, the following return query solution gives exactly back what was needed. 

CREATE OR REPLACE FUNCTION delete_person(_person_id UUID)
    RETURNS TABLE(id UUID)
AS
$$
BEGIN

    -- delete user > person goes cascade
    RETURN QUERY
        WITH CTE
                 AS (DELETE FROM users WHERE user_id = (SELECT user_id FROM persons WHERE person_id = _person_id) RETURNING _person_id)
        SELECT CTE._person_id
        FROM CTE;

END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions