I have made the following query which is working perfect by itself but when i call it inside trigger function i got problem.
select insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), s.code)
FROM "student" as s
where find_st(s.grade)>=5;
insert_new_grade is a function that inserts a new row in a table every time it's being called.
Here is the trigger :
CREATE OR REPLACE FUNCTION insert_d()
RETURNS TRIGGER AS $$
BEGIN
select insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), s.code)
FROM "student" as s
where find_st(s.grade)>=5;
return new;
END;
$$ LANGUAGE plpgsql;
and here is the insert function :
CREATE OR REPLACE FUNCTION insert_new_grade(title0 character(100), prof0 character(11), prof1 character(11))
RETURNS VOID AS $$
BEGIN
INSERT INTO "d_table"(thes0, title, grade, prof, secProf)
VALUES (null, title0, null, prof0, prof1);
END
$$
LANGUAGE 'plpgsql';
Is there a way to make the query work inside the trigger function ? If i use perform instead of select the insert function does not have result. I've read about cursors but I'm new in postgresql and I don't know how to do it. Any help ?
i modifited your trigger function:
check your trigger function in conlose pgAdmin, is visible raise info text
CREATE OR REPLACE FUNCTION insert_d()
RETURNS TRIGGER AS $$
declare
rec record;
BEGIN
for rec in (select * from student s where find_st(s.grade)>=5) loop
raise info 'LOOP code = %',rec.code;
PERFORM insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), rec.code);
end loop;
return new;
END;
$$ LANGUAGE plpgsql;