When calling a stored procedure that contains the following loop in it, get an error
ERROR: missing FROM-clause entry for table "raw_data" SQL state: 42P01
Note that raw_data is not a table.
Stored procedure loop block :
FOR row_data IN SELECT * from temp_alerts_for_today_table
LOOP
PERFORM * FROM temp_mails_for_today_table AS temp
WHERE temp.mailrecipient = row_data.mailrecipient
AND temp.alertconfigurationid = row_data.alertconfigurationid;
IF(NOT FOUND) THEN
INSERT INTO temp_mails_for_today_table(mailrecipient, alertconfigurationid, mailsubject, mailcontent, instrumentlist)
VALUES(row_data.mailrecipient,row_data.alertconfigurationid,row_data.mailsubject,row_data.mailcontent,row_data.instrument);
ELSE
SELECT temp.instrumentlist INTO instrument_list
FROM temp_mails_for_today_table AS temp
WHERE temp.mailrecipient = row_data.mailrecipient
AND temp.alertconfigurationid = row_data.alertconfigurationid;
IF (instrument_list IS NULL) THEN
UPDATE temp_mails_for_today_table
SET instrumentlist = raw_data.instrument
WHERE mailrecipient = row_data.mailrecipient
AND alertconfigurationid = row_data.alertconfigurationid;
ELSE
instrument_list := instrument_list || ',' || raw_data.instrument;
UPDATE temp_mails_for_today_table
SET instrumentlist = instrument_list
WHERE mailrecipient = row_data.mailrecipient
AND alertconfigurationid = row_data.alertconfigurationid;
END IF;
END IF;
END LOOP;
