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'; 
