CODEWITHSUNDEEP
sqlfunctionpostgresqltriggers
fivetech
fivetech

Reputation: 361

How to declare instance of table with different types of column

I'm using PostgreSQL. I need to create a trigger which keeps students' GPA's up-to-date. I declared r variable to loop through the SQL statement. However, I cannot use other table's field instead of Student like r.credits, r.grade. How should I declare this type of variable? 

CREATE OR REPLACE FUNCTION updateGPA() RETURNS TRIGGER AS $$
    DECLARE
    r Student;
    total_credits INT;
    temp INT;
BEGIN
    FOR r IN (SELECT s.sid, c.credits, t.grade FROM Student s, Course c, Take t WHERE t.cid = NEW.cid and t.sid = s.sid)
    LOOP
        total_credits = SUM(r.credits);
        temp = temp + SUM(r.credits * r.grade); 
        UPDATE Student SET gpa = temp/total_credits WHERE Student.sid = r.sid;
    END LOOP;
    RETURN NEW;
END;

Upvotes: 0

Views: 62

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

The simplest is to use the anonymous type record:

DECLARE
  r record

Then put the fields you need in the select statement.

Upvotes: 1

Related Questions