request for advise - Mailing list pgsql-general

From Brian Hirt
Subject request for advise
Date
Msg-id 000d01c16606$83d50fe0$640b0a0a@berkhirt.com
Whole thread Raw
Responses Re: request for advise
Re: request for advise
List pgsql-general
Hello,

I'm looking for people's opinions on what would be the best way to delete
some rows in one table, when the row referencing them in another table is
deleted.  The relationship of the objects is one-to-one.  IE, you will never
have more than one foriegn key for each primary key.  My example contains
two tables; 'list' and 'sample'   The 'list' table is a linked list of
records, and 'sample' is another record that contains a pointer  to the head
of a list.  When a row is deleted from 'sample' I want all of the rows from
'list' deleted that part of the list.    Some things to note, are that the
list has to be deleted in reverse order to avoid RI errors; and I'm not sure
about pgsql's ability to run recursively.  I tried writing a function to
recursively delete the list but it fails with "ERROR: unexpected SELECT
query in exec_stmt_execsql()"

--thanks.

create table list (
 id int,
 next_id int references list(id),
 primary key (id));

create table sample (
 id int,
 list_id int references list(id),
 primary key (id));

insert into list values (1,NULL);
insert into list values (2,1);
insert into list values (3,2);

insert into sample values (1,1);

/* sample function to delete a list -- fails with "unexpected SELECT query
in exec_stmt_execsql()" */
create function delete_list_item(int4) returns int4 as '
DECLARE
 del_next_id int4;
BEGIN
 SELECT  next_id
 INTO   del_next_id
 FROM   list
 WHERE  id = $1;

 IF del_next_id is not null THEN
  SELECT delete_list_item(del_next_id);
 END IF;

 DELETE FROM list WHERE id = $1;

 RETURN 1;
END;
' language 'plpgsql';

/* run this and get exec_stmt_execsql() error */
select delete_list_item(3);




pgsql-general by date:

Previous
From: "Roderick A. Anderson"
Date:
Subject: Re: newbie question
Next
From: cbbrowne@acm.org
Date:
Subject: Re: Sum(time) possible?