Обсуждение: 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
> > 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:

> 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?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

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