Thread: A "cascade on delete" constraints deletes AFTER the source is gone??
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? -- ICQ: 1912453 AIM: VitalyB1984 MSN: tmdagent@hotmail.com Yahoo!: VitalyBe
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/
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 <mike@fuhr.org> 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: tmdagent@hotmail.com Yahoo!: VitalyBe
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/
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, ''YYYYMMDD'');
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
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/