Re: Using a function to delete rows

Поиск
Список
Период
Сортировка
От Oliver Fromme
Тема Re: Using a function to delete rows
Дата
Msg-id 200310091742.h99Hghrg033009@lurza.secnetix.de
обсуждение исходный текст
Ответ на Using a function to delete rows  ("Derrick Betts" <Derrick@grifflink.com>)
Список pgsql-novice
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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Godshall Michael
Дата:
Сообщение: Re: Using a function to delete rows
Следующее
От: Aled Morris
Дата:
Сообщение: output parameters in functions?