Re: Using a function to delete rows

Derrick Betts wrote:
 > How do I create a function that takes as input (int4) and then
 > deletes rows from several tables.  This is what I have tried,
 > but I can't get it to execute:
 >
 > CREATE OR REPLACE FUNCTION public.deleteclient(int4)
 >   RETURNS Void AS
 > '
 > BEGIN
 > Delete from clientinfo where caseid = $1;
 > Delete from caseinfo where caseid = $1;
 > Delete from tracking where caseid = $1;
 > Delete from casenotes where caseid = $1;
 > Delete from creditinfo where caseid = $1;
 > Delete from debts where caseid = $1;
 > Delete from education where caseid = $1;
 > Delete from employer where caseid = $1;
 > Delete from family where caseid = $1;
 > Delete from formeremployer where caseid = $1;
 > Delete from income where caseid = $1;
 > Delete from other where caseid = $1;
 > Delete from specialinterests where caseid = $1;
 > Delete from tracking where caseid = $1;
 > END'
 >   LANGUAGE 'plpgsql' VOLATILE;

Not an actual answer to your question, but in the above design
it would be really useful to have a separate table (lets call
it "cases") which contains all the case IDs, and in all of the
other tables make caseid a foreign key into "cases" with "on
delete cascade".  Then you can just delete a case from the
"cases" table, and all the related entries from all other
tables will be deleted automatically.  There would be no need
for a function like the above one at all.

Just an idea.

Regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"Being really good at C++ is like being really good
at using rocks to sharpen sticks."
        -- Thant Tessman