From: | Abdul Mohammed <imonikemohammed(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Execute command in PL/pgSQL function not executing |
Date: | 2021-11-18 11:24:08 |
Message-ID: | CAEKkz87s6c9BL4s2p3Je_y2iCZy0-2mLdV7_YFJ79iOFvRZdkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello everyone,
Please I am having a problem with a function I am writing. The first part
uses a loop that pulls the values from a column and concatenates them into
a string. This first part works fine. The second part tries to use the
string to build a pivot table using the crosstab function. The function is
as follows:
CREATE OR REPLACE FUNCTION field_values_ct ()
RETURNS VOID AS $$
DECLARE rec RECORD;
DECLARE str text;
BEGIN
str := '"participant_id" integer,';
-- looping to get column heading string
FOR rec IN SELECT DISTINCT text
FROM question
ORDER BY text
LOOP
str := str || '"' || rec.text || '" text' ||',';
END LOOP;
str:= substring(str, 0, length(str));
EXECUTE 'SELECT *
FROM crosstab(''select sp.participant_id, distinct qu.text, sr.text
from survey_progress sp
join question qu
on sp.survey_id = qu.survey_id
join survey_response sr
on qu.id = sr.question_id
where qu.question_type_id = 8
order by 1,2'')
AS final_result ('|| str ||')';
RAISE NOTICE 'Got to the end of the function';
END;
$$ LANGUAGE plpgsql;
The Execute Select statement doesn't seem to execute. There aren't any
error or hint messages either. It only prints a context message as follows:
CONTEXT: PL/pgSQL function field_values_ct() line 15 at EXECUTE
Please I would be very grateful for any hints as to what I could be doing
wrong.
Regards
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-11-18 11:43:36 | Re: Execute command in PL/pgSQL function not executing |
Previous Message | Дмитрий Иванов | 2021-11-18 07:33:40 | Re: pg_restore depending on user functions |