INSERT error in PLPGSQL function

Question

Hi I have been working on this code today after attempting some of the solutions in: Dynamic column names in view (Postgres)

I am getting some errors which I can't seem to solve, I have fixed lots of problems with this but I can't solve this, bear in mind I am very new to this :)

This is the error I am receiving:

ERROR:  missing FROM-clause entry for table "r"
LINE 2:    VALUES (r.country_name, r."Feb-2011", r."Mar-2011", r."Ap...
               ^
QUERY:  INSERT INTO "avg_enroll_rate" (country, "Feb-2011", "Mar-2011", "Apr-2011", "May-2011", "Jun-2011", "Jul-2011", "Aug-2011", "Sep-2011", "Oct-2011", "Nov-2011", "Dec-2011", "Jan-2012")
                    VALUES (r.country_name, r."Feb-2011", r."Mar-2011", r."Apr-2011", r."May-2011", r."Jun-2011",
                    r."Jul-2011", r."Aug-2011", r."Sep-2011", r."Oct-2011", r."Nov-2011",
                    r."Dec-2011", r."Jan-2012")
CONTEXT:  PL/pgSQL function "test" line 83 at EXECUTE statement

And here is my code:

DROP FUNCTION public.test();
create or replace function public.test()
RETURNS void
SECURITY DEFINER
AS $proc$
DECLARE
    month1 text;
    month2 text;
    month3 text;
    month4 text;
    month5 text;
    month6 text;
    month7 text;
    month8 text;
    month9 text;
    month10 text;
    month11 text;
    month12 text;

    sqlStr text;
    sqlStr2 text;
    insertStr text;
    r record;

BEGIN 
    select to_char((current_date - interval '1 Month')::date,'Mon-YYYY') INTO month1;
    select to_char((current_date - interval '2 Month')::date,'Mon-YYYY') INTO month2;
    select to_char((current_date - interval '3 Month')::date,'Mon-YYYY') INTO month3;
    select to_char((current_date - interval '4 Month')::date,'Mon-YYYY') INTO month4;
    select to_char((current_date - interval '5 Month')::date,'Mon-YYYY') INTO month5;
    select to_char((current_date - interval '6 Month')::date,'Mon-YYYY') INTO month6;
    select to_char((current_date - interval '7 Month')::date,'Mon-YYYY') INTO month7;
    select to_char((current_date - interval '8 Month')::date,'Mon-YYYY') INTO month8;
    select to_char((current_date - interval '9 Month')::date,'Mon-YYYY') INTO month9;
    select to_char((current_date - interval '10 Month')::date,'Mon-YYYY') INTO month10;
    select to_char((current_date - interval '11 Month')::date,'Mon-YYYY') INTO month11;
    select to_char((current_date - interval '12 Month')::date,'Mon-YYYY') INTO month12;

    --RAISE NOTICE 'Month1: %', month1;
    --RAISE NOTICE 'Month2: %', month2;

    sqlStr := $$SELECT c.country_name,  
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '12 months' THEN 1 ELSE 0 END ) AS "$$ || month12 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '11 months' THEN 1 ELSE 0 END ) AS "$$ || month11 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '10 months' THEN 1 ELSE 0 END ) AS "$$ || month10 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '9 months' THEN 1 ELSE 0 END ) AS "$$ || month9 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '8 months' THEN 1 ELSE 0 END ) AS "$$ || month8 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '7 months' THEN 1 ELSE 0 END ) AS "$$ || month7 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '6 months' THEN 1 ELSE 0 END ) AS "$$ || month6 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '5 months' THEN 1 ELSE 0 END ) AS "$$ || month5 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '4 months' THEN 1 ELSE 0 END ) AS "$$ || month4 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '3 months' THEN 1 ELSE 0 END ) AS "$$ || month3 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '2 months' THEN 1 ELSE 0 END ) AS "$$ || month2 || $$",
                SUM( CASE WHEN date_trunc('month', "C1".eldate::date) =  date_trunc('month', now()) - interval '1 months' THEN 1 ELSE 0 END ) AS "$$ || month1 || $$"

            FROM country AS c
                INNER JOIN "site" AS s using (country_id)
                INNER JOIN "subject_C1" AS "C1" ON "s"."site_id" = "C1"."site_id"

            GROUP BY 1$$;

    EXECUTE $$DROP TABLE avg_enroll_rate$$;
    EXECUTE $$CREATE TABLE avg_enroll_rate (country VARCHAR(512), "$$ || month12 || $$" bigint, "$$ || month11 || $$" bigint, "$$ 
    || month10 || $$" bigint, "$$ || month9 || $$" bigint, "$$ || month8 || $$" bigint, "$$ || month7 || $$" bigint, "$$ || month6 || $$" bigint, "$$ 
    || month5 || $$" bigint, "$$ || month4 || $$" bigint, "$$ || month3 || $$" bigint, "$$ || month2 || $$" bigint, "$$ || month1 || $$" bigint)$$; 


    FOR r IN
        EXECUTE sqlStr
    LOOP
        RAISE NOTICE 'Record 1: %', r;

        sqlStr2 := $$INSERT INTO "avg_enroll_rate" (country, "$$ || month12 || $$", "$$ || month11  || $$", "$$ ||  month10  || $$", "$$ ||  month9  || $$", "$$ ||  month8  || $$", "$$ ||  month7  || $$", "$$ ||  month6  || $$", "$$ ||  month5  || $$", "$$ ||  month4  || $$", "$$ ||  month3  || $$", "$$ ||  month2  || $$", "$$ ||  month1 || $$")
        VALUES ("r".country_name, r."$$ || month12 || $$", r."$$ || month11 || $$", r."$$ || month10 || $$", r."$$ || month9 || $$", r."$$ || month8 || $$",
        r."$$ || month7 || $$", r."$$ || month6 || $$", r."$$ || month5 || $$", r."$$ || month4 || $$", r."$$ || month3 || $$",
        r."$$ || month2 || $$", r."$$ || month1 || $$")$$;


        RAISE NOTICE 'SQL = [%]', sqlStr2;

        EXECUTE sqlStr2; 

    END LOOP;


END
$proc$
LANGUAGE 'PLPGSQL';

Erwin Brandstetter · Accepted Answer · 2012-02-28 15:08:30Z

1

You are bumping into this restriction of executing dynamic SQL in plpgsql. I quote the manual:

No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.

So you have put the values into the SQL string as text literals (Careful about SQL injection! Use quote_literal()) or you can utilize the USING clause - which is preferrable in most cases. You can find more about that at the same spot in the manual.

answered Feb 28, 2012 at 15:08

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aydin Hassan Over a year ago

Yeah I have just found this out, it is working now using quote_literal, thanks

Collectives™ on Stack Overflow

INSERT error in PLPGSQL function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related