0

I have a function that calls a sub-function as part of the function. When running the sub-function with the same data it throws an error, when running the main function it doesn't. I would have expected it to throw an error in both cases, why is this?

Here are the functions (I removed some details that didn't seem relevant and changed some table names):

CREATE OR REPLACE FUNCTION delete_old_cases_people() RETURNS TEXT AS $$
DECLARE
    return_value TEXT;
    people_deleted INTEGER;

BEGIN

    --Some other stuff here that calls some other functions, inserts things, etc.. unlikely to be related to the issue

    SELECT delete_people(
        ARRAY(
            SELECT DISTINCT a.people_id FROM view_people_to_delete a
        )
    ) INTO people_deleted;

    RETURN 'People Deleted: ' || COALESCE(TRIM(people_deleted::TEXT),'Error') || ';';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_people(peoplearray INTEGER[]) RETURNS INTEGER AS $$
DECLARE
    return_value INTEGER;

BEGIN

    --Some other stuff here that calls some other functions, inserts things, etc.. unlikely to be related to the issue

WITH deleted AS
    (DELETE FROM people WHERE people.kindid = ANY(peoplearray) RETURNING *)
SELECT COUNT(*)::INTEGER INTO return_value FROM deleted;

RETURN return_value;
END;
$$ LANGUAGE plpgsql;

Running SELECT delete_old_cases_people(); returns "People Deleted: 0", which is true I guess but running SELECT delete_people(ARRAY(SELECT DISTINCT a.people_id FROM view_people_to_delete a)); throws an error. I would have expected SELECT delete_old_cases_people(); to throw an error as well, why is this?

FYI: SELECT version(); returns: "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit" I think this is not fully updated, but I don't have direct control over this version.

  • 3
    What is the error? – Clodoaldo Jan 6 '16 at 18:00
  • @Clodoaldo the delete violated a foreign key constraint for certain records. I was able to resolve this in the delete_people function. I was assuming that this wasn't related, but perhaps it is. – joyleak Jan 6 '16 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.