1

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.

3
  • poprobuj declare conference record; i for conference in (select * from conferenceset) loop Commented Jun 2, 2016 at 12:46
  • Thank you very much ! This eliminated the error. However at "conference.participants_count := SELECT COUNT(*)" I have a syntax error again. Do u know how to correct that ? Commented Jun 2, 2016 at 13:00
  • not sure if I saw all flaws - try my answer. btw you can select count(*) into variable_name Commented Jun 2, 2016 at 13:36

1 Answer 1

2
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';
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.