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