Re: [GENERAL] A cascade on delete constraints deletes AFTER the source is gone??
On Mon, Dec 20, 2004 at 09:56:35AM +0200, Vitaly Belman wrote: I have two tables. Books and Book_Authors (which links between book_id and author_id). Is there a third table, perhaps Authors? Book_authors has a foreign key on book_id to the Books table. On key violation it is set to delete the rows (if a book is deleted, it should't be linked to any authors). In Book_Authors I also have a trigger on DELETE. When a book is unlinked from an author, then the author vote_count should be reduced (as the author vote_count is the sum of all votes of his books). Which table has vote_count -- the Authors table that I'm guessing exists? Where does vote_count's value come from? A field in Books? Is there a reason you're maintaining vote_count instead of querying for it with an aggregate like SUM or COUNT? The problem is that when a book is deleted and then the trigger tried to get the number of it votes, it returns NULL, as the book is already gone and so its data. If it was a simple matter of triggers I could play with BEFORE/AFTER, but since it is constraints issue, it seems to be all happening AFTER the deletion. Is there a reason you can't maintain vote_count with a trigger on Books? Could you post a minimal but complete example (CREATE statements and INSERT or COPY statements with sample data) so we can get a better idea of what you're trying to do? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A cascade on delete constraints deletes AFTER the source is gone??
Hi All! I need to make function which emulates table and returns setof columns of this table for example, i'm making query: SELECT * FROM my_table(user_id) and function CREATE OR REPLACE FUNCTION my_table (integer) RETURNS setof text AS ' DECLARE check_date date; max_date date; r record; BEGIN max_date := $2; check_date := $3; WHILE (check_date = max_date) LOOP table_nam := ''table_''||to_char(check_date, ''MMDD''); FOR r IN EXECUTE ''SELECT COUNT(*) as cnt, AVG(amount) as avg_amount, SUM(amount) as sum_amount FROM ''||table_nam||'' WHERE user_id = ''||$1||'' '' LOOP RETURN NEXT r.cnt || r.avg_amount || r.sum_amount; END LOOP; check_date := check_date - interval ''1 day''; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; As a result i need to get a records, and will be able to use each column from it But my function doesn't work What is wrong? Thanx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] A cascade on delete constraints deletes AFTER the source is gone??
On Mon, Dec 20, 2004 at 04:31:59PM +0300, ON.KG wrote: I need to make function which emulates table and returns setof columns of this table Please start a new thread with a descriptive subject -- the message you posted was a followup to a thread covering a different topic. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] A cascade on delete constraints deletes AFTER the source is gone??
On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote: I noticed that when you do a constraint and tell it cascade on delete, it will do so only AFTER that the source is deleted. Can I tell it somehow to cascade BEFORE the source is gone? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A cascade on delete constraints deletes AFTER the source is gone??
It's a bit complex. I have two tables. Books and Book_Authors (which links between book_id and author_id). Book_authors has a foreign key on book_id to the Books table. On key violation it is set to delete the rows (if a book is deleted, it should't be linked to any authors). In Book_Authors I also have a trigger on DELETE. When a book is unlinked from an author, then the author vote_count should be reduced (as the author vote_count is the sum of all votes of his books). The problem is that when a book is deleted and then the trigger tried to get the number of it votes, it returns NULL, as the book is already gone and so its data. If it was a simple matter of triggers I could play with BEFORE/AFTER, but since it is constraints issue, it seems to be all happening AFTER the deletion. On Sun, 19 Dec 2004 12:23:09 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote: I noticed that when you do a constraint and tell it cascade on delete, it will do so only AFTER that the source is deleted. Can I tell it somehow to cascade BEFORE the source is gone? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
