Execute command in PL/pgSQL function not executing
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
Re: Execute command in PL/pgSQL function not executing
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
wrote:
>
>
> čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <
> imonikemoham...@gmail.com> 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
>>
>>
>>
Re: Execute command in PL/pgSQL function not executing
Thank you very much for your response Tom. I will follow your
recommendations. As for the 3rd one, I am actually trying to output the
pivot table I am trying to create using crosstab. I read somewhere that you
could have a table as a return type. I, therefore tried using the pivot
table alias as the return type but got an error along lines of the alias
not being recognized.
Much regards
On Fri, Nov 19, 2021 at 12:59 PM Tom Lane wrote:
> Abdul Mohammed writes:
> > 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:
>
> I tried this, and after creating a dummy "question" table I got
>
> ERROR: structure of query does not match function result type
> DETAIL: Returned type integer does not match expected type text in column
> 1.
> CONTEXT: SQL statement "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 ("participant_id" integer,"what" text,"when"
> text,"who" text)"
> PL/pgSQL function survey_ct() line 15 at RETURN QUERY
>
> It's slightly surprising to me that this problem gets detected
> before noticing that the embedded query is invalid (it's
> syntactically incorrect because of the bogus placement of
> "distinct", even before getting to the fact that I didn't
> make a survey_progress table). Nonetheless, it's a real
> problem with the way you're trying to use crosstab().
> Given that the output of crosstab() will be an integer
> column followed by some text columns, you can't just
> "return query" in a calling function whose output is
> specified to be a single text column.
>
> Anyway, I'd make the following recommendations:
>
> 1. You need to get used to Postgres error message layout.
> You're apparently focusing only on the last line of context,
> which is about the least important part of the report.
>
> 2. I'd suggest debugging the base query before trying to
> wrap it in a crosstab() call, and then testing the crosstab()
> manually before you wrap it in a plpgsql function. That
> would give you a better feeling for the work that
> the plpgsql function has to do.
>
> 3. I kind of doubt that outputting a single text column
> is really the end result you want here. How is it
> meaningful to be crosstab'ing multiple questions if
> that's to be the end result?
>
> regards, tom lane
>
Re: Execute command in PL/pgSQL function not executing
Many thanks for the explanations David. My failed attempts and your explanations have forced me to realise that I need to spend some time learning to walk before attempting to run. Much regards On Fri, Nov 19, 2021 at 1:31 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed > wrote: > >> The second part tries to use the string to build a pivot table using the >> crosstab function. >> > > Aside from the simple learning curve on how to write functions in > PostgreSQL you also are dealing with the fact that you are dealing with a > query that has a variable number of columns and that is just not something > that PostgreSQL allows. It is in fact the reason the crosstab function > itself has to use the convoluted record return syntax where the caller has > to declare how many columns the function is going to return. You are > trying to get around this by having the function scan the table at runtime > to figure out which columns it needs to declare. But that won't work > because the function itself still has to know how many columns it is going > to output when it is called. > > Personally I've found two solutions to this. Do the dynamic part in > application code and just send the dynamic SQL to the server for > execution. Or, turn your output into a container type (I've used CSV in > the past but JSON probably works better) and just return the complex value > from the function; then the application just has to deal with a simple > decomposing of the complex value into the final table it represents. > > David J. >
