request for advise

request for advise

Post by Brian Hir » Wed, 07 Nov 2001 01:48:57



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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

request for advise

Post by Stephan Sza » Wed, 07 Nov 2001 05:00:08



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

Quote:> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

request for advise

Post by Stephan Sza » Wed, 07 Nov 2001 07:00:08



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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

1. request for advise

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

2. why is update statistics getting slower and slower?

3. Advise on MSQL certification requested

4. reSync method

5. Advise Requested: Connecting AP/SCO to Novell 3.11

6. WA, OR-JAVA DEVELOPER WITH KNOWLEDGE (PERMANENT)

7. Consulting advise requested

8. Database updates very slow with JDBC and SQL-Server 7.0

9. Encryption Performance Advise Request

10. ADVISE NEEDED: How to migrate :ADVISE NEEDED

11. Advise requested - Updating a database used on at a web site???

12. Help, Advise requested, What is the Easiest or Best way to....

13. Please advise!!