Re: can a trigger on insert -> update other tables?

On Sun, Mar 04, 2001 at 04:52:16AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > [...]
> >
> > CREATE TRIGGER _rating_propagate
> > BEFORE INSERT ON _rating
> > FOR EACH ROW EXECUTE PROCEDURE _rating_propagate();
> >
> > ERROR: NEW used in non-rule function
> >
> > (this doesn't happen until an insert actually activates the
> > trigger, so that plpgsql finally 'sees' the code of the procedure
> > and tries to execute it.) so what else could i try?
> >
> > > And remember to do
> > > RETURN new
> > > somewhere.
> >
> > right. but now i have a different problem... :)
>
> Can you add the line
>
> #option dump
>
> at the very top of your function (before DECLARE) and send me
> the debug output from the Postmaster?

sure! if you like, i can post the source SQL for the ratings
table and its views and functions and triggers and rules...

-- begin LOGFILE --

Execution tree of successfully compiled PL/pgSQL function _rating_propagate:

Functions data area:
entry 0: REC new
entry 1: REC old
entry 2: VAR tg_name type name (typoid 19) atttypmod -1
entry 3: VAR tg_when type text (typoid 25) atttypmod -1
entry 4: VAR tg_level type text (typoid 25) atttypmod -1
entry 5: VAR tg_op type text (typoid 25) atttypmod -1
entry 6: VAR tg_relid type oid (typoid 26) atttypmod -1
entry 7: VAR tg_relname type name (typoid 19) atttypmod -1
entry 8: VAR tg_nargs type int4 (typoid 23) atttypmod -1
entry 9: VAR found type bool (typoid 16) atttypmod -1
entry 10: VAR opinion type bpchar (typoid 1042) atttypmod 5
entry 11: VAR courseID type int4 (typoid 23) atttypmod -1
entry 12: VAR topicID type int4 (typoid 23) atttypmod -1
entry 13: VAR eduID type int4 (typoid 23) atttypmod -1
entry 14: VAR profID type int4 (typoid 23) atttypmod -1
entry 15: RECFIELD rating of REC 0
entry 16: ROW *internal* fields courseID=var 11
entry 17: RECFIELD course of REC 0
entry 18: ROW *internal* fields topicID=var 12
entry 19: ROW *internal* fields eduID=var 13
entry 20: ROW *internal* fields profID=var 14
entry 21: RECFIELD who of REC 0
entry 22: RECFIELD student of REC 0
entry 23: RECFIELD who of REC 0
entry 24: RECFIELD student of REC 0
entry 25: RECFIELD who of REC 0
entry 26: RECFIELD student of REC 0
entry 27: RECFIELD who of REC 0
entry 28: RECFIELD student of REC 0
entry 29: RECFIELD who of REC 0
entry 30: RECFIELD rating of REC 0
entry 31: RECFIELD student of REC 0
entry 32: RECFIELD who of REC 0
entry 33: RECFIELD who of REC 0
entry 34: RECFIELD student of REC 0

Functions statements:
8:BLOCK <<*unnamed*>>
9: ASSIGN var 10 := 'SELECT upper(substring( $1 from 1 for 1)) {$1=15}'
10: SELECT 'SELECT _course.id WHERE _course.id = $1 {$1=17}'
target = 16 *internal*
12: SELECT 'SELECT _topic.id WHERE _topic.id = _course.topic AND _course.id = $1 {$1=11}'
target = 18 *internal*
14: SELECT 'SELECT _edu.id WHERE _edu.id = _topic.edu AND _topic.id = $1 {$1=12}'
target = 19 *internal*
17: SELECT 'SELECT _prof.who WHERE _prof.who = $1 AND _prof.course = $2 {$1=21, $2=11}'
target = 20 *internal*
19: IF 'SELECT NOT $1 {$1=9}' THEN
20: RAISE ''Unable to match professor to class, for rating''
ELSE
ENDIF
22: IF 'SELECT $1 = 'A' {$1=10}' THEN
24: EXECSQL 'UPDATE _course SET a = a + 1 WHERE _course.id = $1 {$1=11}'
25: EXECSQL 'UPDATE _topic SET a = a + 1 WHERE _topic.id = $1 {$1=12}'
26: EXECSQL 'UPDATE _edu SET a = a + 1 WHERE _edu.id = $1 {$1=13}'
27: EXECSQL 'UPDATE _student SET a = a + 1 WHERE _student.who = $1 {$1=22}'
28: EXECSQL 'UPDATE _faculty SET a = a + 1 WHERE _faculty.who = $1 AND _faculty.edu = $2 {$1=23, $2=13}'
29: EXECSQL 'UPDATE _prof SET a = a + 1 WHERE _prof.who = $1 AND _prof.course = $2 {$1=14, $2=11}'
ELSE
32: IF 'SELECT $1 = 'C' {$1=10}' THEN
33: EXECSQL 'UPDATE _course SET c = c + 1 WHERE _course.id = $1 {$1=11}'
34: EXECSQL 'UPDATE _topic SET c = c + 1 WHERE _topic.id = $1 {$1=12}'
35: EXECSQL 'UPDATE _edu SET c = c + 1 WHERE _edu.id = $1 {$1=13}'
36: EXECSQL 'UPDATE _student SET c = c + 1 WHERE _student.who = $1 {$1=24}'
37: EXECSQL 'UPDATE _faculty SET c = c + 1 WHERE _faculty.who = $1 AND _faculty.edu = $2 {$1=25, $2=13}'
38: EXECSQL 'UPDATE _prof SET c = c + 1 WHERE _prof.who = $1 AND _prof.course = $2 {$1=14, $2=11}'
ELSE
41: IF 'SELECT $1 = 'D' {$1=10}' THEN
42: EXECSQL 'UPDATE _course SET d = d + 1 WHERE _course.id = $1 {$1=11}'
43: Couldn't open /var/lib/postgres/data/pg_options
010304.13:07:18.501 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.513 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.519 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.533 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.547 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.566 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.577 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.590 [15216] NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.684 [15216] NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index '_rating_pkey' for table '_rating'
010304.13:07:18.684 [15216] NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
EXECSQL 'UPDATE _topic SET d = d + 1 WHERE _topic.id = $1 {$1=12}'
44: EXECSQL 'UPDATE _edu SET d = d + 1 WHERE _edu.id = $1 {$1=13}'
45: EXECSQL 'UPDATE _student SET d = d + 1 WHERE _student.who = $1 {$1=26}'
46: EXECSQL 'UPDATE _faculty SET d = d + 1 WHERE _faculty.who = $1 AND _faculty.edu = $2 {$1=27, $2=13}'
47: EXECSQL 'UPDATE _prof SET d = d + 1 WHERE _prof.who = $1 AND _prof.course = $2 {$1=14, $2=11}'
ELSE
50: IF 'SELECT $1 = 'F' {$1=10}' THEN
51: EXECSQL 'UPDATE _course SET f = f + 1 WHERE _course.id = $1 {$1=11}'
52: EXECSQL 'UPDATE _topic SET f = f + 1 WHERE _topic.id = $1 {$1=12}'
53: EXECSQL 'UPDATE _edu SET f = f + 1 WHERE _edu.id = $1 {$1=13}'
54: EXECSQL 'UPDATE _student SET f = f + 1 WHERE _student.who = $1 {$1=28}'
55: EXECSQL 'UPDATE _faculty SET f = f + 1 WHERE _faculty.who = $1 AND _faculty.edu = $2 {$1=29, $2=13}'
56: EXECSQL 'UPDATE _prof SET f = f + 1 WHERE _prof.who = $1 AND _prof.course = $2 {$1=14, $2=11}'
ELSE
58: ASSIGN var 30 := 'SELECT 'B''
60: EXECSQL 'UPDATE _course SET b = b + 1 WHERE _course.id = $1 {$1=11}'
61: EXECSQL 'UPDATE _topic SET b = b + 1 WHERE _topic.id = $1 {$1=12}'
62: EXECSQL 'UPDATE _edu SET b = b + 1 WHERE _edu.id = $1 {$1=13}'
63: EXECSQL 'UPDATE _student SET b = b + 1 WHERE _student.who = $1 {$1=31}'
64: EXECSQL 'UPDATE _faculty SET b = b + 1 WHERE _faculty.who = $1 AND _faculty.edu = $2 {$1=32, $2=13}'
65: EXECSQL 'UPDATE _prof SET b = b + 1 WHERE _prof.who = $1 AND _prof.course = $2 {$1=14, $2=11}'
ENDIF
ENDIF
ENDIF
ENDIF
70: EXECSQL 'delete from trak'
71: EXECSQL 'insert into trak values( $1 , $2 , $3 , $4 , $5 ) {$1=13, $2=12, $3=11, $4=33, $5=34}'
72: RETURN record 0
END -- *unnamed*

End of execution tree of function _rating_propagate

-- end LOGFILE --

of course, these tables are intricately related to others not
mentioned here... but i'm hoping you can rub your eyeballs over
this and think to yourself "what a newbie! if he'd only ditch the
clavis and use the frammistat, it'd be so simple..." :)

if so, bring it on!

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!