Very very new here and to PGSQL creating an app using flutterflow. I am trying to create a trigger function that will update a column holes when a new row is added to the scorecard table using a subquery of another table called courses.
I could be doing this completely wrong but any ideas would be appreciated, the function I created is as below:
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
Update scorecards
SET holes=
(Select holes from courses
Where courses.name=scorecards.course_name);
return new;
end;
$$;
The trigger I created to call this is this:
CREATE TRIGGER scorecards2
AFTER UPDATE ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
At the moment it is creating the row in the supabase table but it is not triggering the function to update the holes field. I have tried this as a before and after update but I cannot seem to get this to work.
As I said above, any help would be really appreciated!
A semi-answer, as it outlines what I think you want to achieve.:
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
NEW.holes = (Select holes from courses
Where courses.name=NEW.course_name LIMIT 1);
return new;
end;
$$;
--UPDATE to answer
CREATE TRIGGER scorecards2
BEFORE INSERT ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
Note the LIMIT 1 in the query. You can only use one value. So either use a LIMIT or construct a query you know only returns one value.