Declare a variable of temporary table in stored procedure in PL/pgSQL

Question

I receive this error to begin with:

ERROR:  syntax error at or near "conference"
LINE 19: FOR conference IN conferenceset 

Here's the function:

CREATE OR REPLACE FUNCTION due_payments_to_suppliers_previous_month() 
RETURNS TABLE(supplier varchar,due_amount numeric) 
AS $$
DECLARE 

BEGIN

 CREATE TABLE conferenceset AS -- temporary table, so I can store the result set
    SELECT 
    conference.conference_supplier_id,
    conference.id AS conferenceid,
    conference.price_per_person, 
    0 AS participants_count,
    400 AS deduction_per_participant,
    0 AS total_amount 
    FROM Conference WHERE --- date_start has to be from the month before
            date_start >= date_trunc('month', current_date - interval '1' month)
            AND 
            date_start < date_trunc('month', current_date);

FOR conference IN conferenceset
LOOP
---fill up the count_participants column for the conference
conference.participants_count := 
    SELECT COUNT(*)
    FROM participant_conference JOIN conferenceset
    ON participant_conference.conference_id = conferenceset.conferenceid;
---calculate the total amount for that conference
conference.total_amount := somerec.participants_count*(conference.price_per_person-conference.deduction_per_participant);
END LOOP;

----we still don't have the name of the suppliers of these conferences
CREATE TABLE finalresultset AS -- temporary table again  
    SELECT conference_supplier.name, conferenceset.total_amount
    FROM conferenceset JOIN conference_supplier
    ON conferenceset.conference_supplier_id = conference_supplier.id

----we have conference records with their amounts and suppliers' names scattered all over this set
----return the result with the suppliers' names extracted and their total amounts calculated
FOR finalrecord IN (SELECT name,SUM(total_amount) AS amount FROM finalresultset GROUP BY name)
LOOP
supplier:=finalrecord.name;
due_amount:=finalrecord.amount; 
RETURN NEXT; 
END LOOP;  

END; $$
LANGUAGE 'plpgsql';

I don't know how and where to declare the variables that I need for the two FOR loops that I have: conference as type conferenceset and finalrecord whose type I'm not even sure of. I guess nested blocks will be needed as well. It's my first stored procedure and I need help. Thank you.

Answer 1

CREATE OR REPLACE FUNCTION due_payments_to_suppliers_previous_month() 
RETURNS TABLE(supplier varchar,due_amount numeric) 
AS $$
DECLARE 
  conference record;
  finalrecord record;
BEGIN

 CREATE TABLE conferenceset AS -- temporary table, so I can store the result set
    SELECT 
    conference.conference_supplier_id,
    conference.id AS conferenceid,
    conference.price_per_person, 
    0 AS participants_count,
    400 AS deduction_per_participant,
    0 AS total_amount 
    FROM Conference WHERE --- date_start has to be from the month before
            date_start >= date_trunc('month', current_date - interval '1' month)
            AND 
            date_start < date_trunc('month', current_date);

FOR conference IN (select * from conferenceset)
LOOP
---fill up the count_participants column for the conference
conference.participants_count = (
    SELECT COUNT(*)
    FROM participant_conference JOIN conferenceset
    ON participant_conference.conference_id = conferenceset.conferenceid
);
---calculate the total amount for that conference
conference.total_amount = somerec.participants_count*(conference.price_per_person-conference.deduction_per_participant);
END LOOP;

----we still don't have the name of the suppliers of these conferences
CREATE TABLE finalresultset AS -- temporary table again  
    SELECT conference_supplier.name, conferenceset.total_amount
    FROM conferenceset JOIN conference_supplier
    ON conferenceset.conference_supplier_id = conference_supplier.id

----we have conference records with their amounts and suppliers' names scattered all over this set
----return the result with the suppliers' names extracted and their total amounts calculated
FOR finalrecord IN (SELECT name,SUM(total_amount) AS amount FROM finalresultset GROUP BY name)
LOOP
supplier = finalrecord.name;
due_amount = finalrecord.amount; 
RETURN NEXT; 
END LOOP;  

END; $$
LANGUAGE 'plpgsql';