Re: Execute command in PL/pgSQL function not executing

[Abdul Mohammed <imonikemohammed@xxxxxxxxx>]

Sorry for the late response and thank you Pavel for answering. This is my first exposure to pgsql, so please bear with me. I am still getting the Context message.  Here is the modified function:

CREATE OR REPLACE FUNCTION survey_ct ()
RETURNS SETOF text 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));

    RETURN QUERY 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;

This gives: CONTEXT:  PL/pgSQL function survey_ct() line 15 at RETURN QUERY

Regards

On Thu, Nov 18, 2021 at 3:44 AM Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <imonikemohammed@xxxxxxxxx> napsal:

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.

This is not MS SQL - result of last query is not result of function.

When you want to see result, you should to use RETURN statement - in this case RETURN QUERY EXECUTE, and your function should to return SETOF text instead VOID.

Regards

Pavel Stehule

Regards