Thread: Using a function to delete rows

-----Original Message-----
From: Derrick Betts [mailto:Derrick@grifflink.com]
Sent: Thursday, October 09, 2003 12:25 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Using a function to delete rows

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;

 

Thanks,

Derrick

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

I'm adding foreign keys to some exixting tables that reference and master
key table as outlined:

CREATE TABLE public.clientinfo
(
  clientid int4 NOT NULL DEFAULT nextval('seq_clientid'::text),
  caseid int4 NOT NULL DEFAULT nextval('seq_caseid'::text),
  clfirst varchar(30),
  ...
  CONSTRAINT clientinfo_pkey PRIMARY KEY (caseid)
) WITH OIDS;

Now...the following table allowed the creation of a foreign key:
CREATE TABLE public.debts
(
  debtid int4 NOT NULL DEFAULT nextval('seq_debtid'::text),
  caseid int4 NOT NULL,
  debttype varchar(20),
  ...
  CONSTRAINT pk_debts PRIMARY KEY (debtid),
  CONSTRAINT fk_debts FOREIGN KEY (caseid) REFERENCES public.clientinfo
(caseid) ON UPDATE CASCADE ON DELETE CASCADE
) WITH OIDS;

The following tabel did NOT allow the creation of a foreign key:
CREATE TABLE public.casenotes
(
  noteid int4 NOT NULL DEFAULT nextval('seq_casenotes'::text),
  agentid int4 NOT NULL,
  caseid int4 NOT NULL,
  ...
  CONSTRAINT pk_casenotes PRIMARY KEY (noteid)
) WITH OIDS;

The command I attempted to execute was:
alter table casenotes
add constraint fk_casenotes foreign key (caseid) references
public.clientinfo (caseid) on update cascade on delete cascade;

The error returned was:
ERROR: fk_casenotes referential integrity violation - key referenced from
casenotes not found in clientinfo

Why will it work on some tables and not others?  The "failing" table was
created in the same manner as the others.

Any ideas?

Thanks,
Derrick

Derrick Betts wrote:
 > [...]
 > The command I attempted to execute was:
 > alter table casenotes
 > add constraint fk_casenotes foreign key (caseid) references
 > public.clientinfo (caseid) on update cascade on delete cascade;
 >
 > The error returned was:
 > ERROR: fk_casenotes referential integrity violation - key referenced from
 > casenotes not found in clientinfo
 >
 > Why will it work on some tables and not others?  The "failing" table was
 > created in the same manner as the others.

The "failing" table probably already contains one or more
rows with caseid values which are not in the clientinfo
table.  You cannot add a constraint if the table already
violates it.  This command will tell you the rows which
need to be fixed or deleted:

select * from casenotes where caseid not in
(select caseid from clientinfo);

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.

"File names are infinite in length, where infinity is set to 255 characters."
        -- Peter Collinson, "The Unix File System"