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);
