Stephan,
Thanks for the reply. The ON DELETE CASCADE does the opposite of what i
want. ie, you delete a primary key, and the foriegn key is deleted. In my
sample, you are deleting the foriegn key, not the primary key.
The function i included in the original message does select into a variable
and it does delete the list row, but if the list has a next_id and the
function is called recursively, that's when i get a stmt_execsql error.
--brian
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: <pgsql-general@postgresql.org>; "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Monday, November 05, 2001 10:02 AM
Subject: Re: [GENERAL] 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.
>