request for advise
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);
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
Show quoted text
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.
Import Notes
Reference msg id not found: 20011105090106.N40711-100000@megazone23.bigpanda.com | Resolved by subject fallback
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.
On Mon, 5 Nov 2001, Brian Hirt wrote:
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.
I meant that I think this line:
SELECT delete_list_item(del_next_id);
needs to be something like
SELECT INTO foo delete_list_item(del_next_id);
or maybe
EXECUTE ''select delete_list_item(del_next_id)'';
"Brian Hirt" <bhirt@mobygames.com> writes:
I tried writing a function to
recursively delete the list but it fails with "ERROR: unexpected SELECT
query in exec_stmt_execsql()"
If you want to evaluate a SQL query and discard the results, plpgsql
wants you to say "PERFORM foo", not "SELECT foo".
Enough people have been confused by this that I think we ought to do
something: either allow SELECT with no destination, or at least reword
the error message to mention PERFORM.
Comments?
regards, tom lane
Tom Lane wrote:
"Brian Hirt" <bhirt@mobygames.com> writes:
I tried writing a function to
recursively delete the list but it fails with "ERROR: unexpected SELECT
query in exec_stmt_execsql()"If you want to evaluate a SQL query and discard the results, plpgsql
wants you to say "PERFORM foo", not "SELECT foo".Enough people have been confused by this that I think we ought to do
something: either allow SELECT with no destination, or at least reword
the error message to mention PERFORM.Comments?
I think re-wording the error message is the way to go here. It's easy
and doesn't unexpectedly change functionality.
:-)
Regards and best wishes,
Justin Clift
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi