Re: request for advise

On Mon, 5 Nov 2001, Brian Hirt wrote:

> 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

I'm assuming that you've tried adding a ON DELETE CASCADE to the
references constraints and had that fail, right?

> 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()"

I think you need to SELECT into a variable, so probably something like
SELECT INTO tmpresult ...; and a tmpresult variable may do it.