Обсуждение: 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;
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
čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <imonikemohammed@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 EXECUTEPlease 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
I am working on a large Oracle to Postgres migration.
The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it.
"EXECUTE" has the same functionality in Postgres.
"EXECUTE" has the same functionality in Postgres.
For example:
CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
RETURN VARCHAR2
AS
v_sql VARCHAR2(1000);
v_name VARCHAR2(30);
BEGIN
v_sql := 'SELECT name FROM employees';
RETURN VARCHAR2
AS
v_sql VARCHAR2(1000);
v_name VARCHAR2(30);
BEGIN
v_sql := 'SELECT name FROM employees';
v_sql := v_sql ||' WHERE employee_number = '||p_emp_no;
EXECUTE IMMEDIATE v_sql INTO v_name;
RETURN v_name;
END;
/
EXECUTE IMMEDIATE v_sql INTO v_name;
RETURN v_name;
END;
/
CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
RETURN VARCHAR2
AS
v_name VARCHAR2(30);
BEGIN
SELECT name INTO v_name FROM employees
RETURN VARCHAR2
AS
v_name VARCHAR2(30);
BEGIN
SELECT name INTO v_name FROM employees
WHERE employee_number = p_emp_no;
RETURN v_name;
END;
/
RETURN v_name;
END;
/
These are oversimplified samples of some very complex queries I need to migrate.
How does the Postgres optimizer handle these 2 formats?
Which format is likely to perform better?
Which format is likely to perform better?
Thanks
Dave
Dave
DAVID ROTH schrieb am 18.11.2021 um 15:15:
> I am working on a large Oracle to Postgres migration.
> The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it.
> "EXECUTE" has the same functionality in Postgres.
>
> For example:
> CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
> RETURN VARCHAR2
> AS
> v_sql VARCHAR2(1000);
> v_name VARCHAR2(30);
> BEGIN
> v_sql := 'SELECT name FROM employees';
> v_sql := v_sql ||' WHERE employee_number = '||p_emp_no;
> EXECUTE IMMEDIATE v_sql INTO v_name;
> RETURN v_name;
> END;
> /
>
> CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
> RETURN VARCHAR2
> AS
> v_name VARCHAR2(30);
> BEGIN
> SELECT name INTO v_name FROM employees
> WHERE employee_number = p_emp_no;
> RETURN v_name;
> END;
> /
>
> These are oversimplified samples of some very complex queries I need to migrate.
>
> How does the Postgres optimizer handle these 2 formats?
> Which format is likely to perform better?
The query does not use any dynamic parts, so EXECUTE is not needed to begin with.
(not even in the Oracle implementation)
For functions that just encapsulate a SQL query, a "language sql" function might be better:
CREATE OR REPLACE FUNCTION djr_foo_fnc (p_emp_no integer)
RETURNS text
AS
$$
SELECT name
FROM employees
WHERE employee_number = p_emp_no;
$$
language sql
rows 1;
They are optimized together with the calling function which can be an advantage
but doesn't have to be in all cases.
If the function is called frequently avoiding the overhead of PL/pgSQL can make
a difference though.
Agreed. The code I am migrating uses the EXECUTE immediate form for almost everything. The path of least resistance for me is tojust copy the code to Postgres and change "EXECUTE IMMEDIATE" to "EXECUTE". I am asking about performance differences to see if it is worth converting code that does not have any dynamic elements. I am also hoping to get a better understanding of the way the Postgres optimizer works. Any links you could suggest for thiswould be appreciated. > On 11/18/2021 9:27 AM Thomas Kellerer <shammat@gmx.net> wrote: > > > DAVID ROTH schrieb am 18.11.2021 um 15:15: > > I am working on a large Oracle to Postgres migration. > > The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it. > > "EXECUTE" has the same functionality in Postgres. > > > > For example: > > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) > > RETURN VARCHAR2 > > AS > > v_sql VARCHAR2(1000); > > v_name VARCHAR2(30); > > BEGIN > > v_sql := 'SELECT name FROM employees'; > > v_sql := v_sql ||' WHERE employee_number = '||p_emp_no; > > EXECUTE IMMEDIATE v_sql INTO v_name; > > RETURN v_name; > > END; > > / > > > > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) > > RETURN VARCHAR2 > > AS > > v_name VARCHAR2(30); > > BEGIN > > SELECT name INTO v_name FROM employees > > WHERE employee_number = p_emp_no; > > RETURN v_name; > > END; > > / > > > > These are oversimplified samples of some very complex queries I need to migrate. > > > > How does the Postgres optimizer handle these 2 formats? > > Which format is likely to perform better? > > The query does not use any dynamic parts, so EXECUTE is not needed to begin with. > (not even in the Oracle implementation) > > For functions that just encapsulate a SQL query, a "language sql" function might be better: > > CREATE OR REPLACE FUNCTION djr_foo_fnc (p_emp_no integer) > RETURNS text > AS > $$ > SELECT name > FROM employees > WHERE employee_number = p_emp_no; > $$ > language sql > rows 1; > > > They are optimized together with the calling function which can be an advantage > but doesn't have to be in all cases. > > If the function is called frequently avoiding the overhead of PL/pgSQL can make > a difference though.
DAVID ROTH <adaptron@comcast.net> writes:
> The code I am migrating uses the EXECUTE immediate form for almost everything. The path of least resistance for me
isto just copy the code to Postgres and change "EXECUTE IMMEDIATE" to "EXECUTE".
> I am asking about performance differences to see if it is worth converting code that does not have any dynamic
elements.
Yes, absolutely. (I would've imagined that that habit is pretty awful
for performance on Oracle, too.)
Having said that, the golden rule in such things is to get it to work
first, and make it fast later. I wouldn't worry about cleaning up
unnecessary EXECUTE usage till you have a working port.
regards, tom lane
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;
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@gmail.com> wrote:
čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <imonikemohammed@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 EXECUTEPlease 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.RegardsPavel StehuleRegards
Abdul Mohammed <imonikemohammed@gmail.com> 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
On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed <imonikemohammed@gmail.com> 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.
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 <tgl@sss.pgh.pa.us> wrote:
Abdul Mohammed <imonikemohammed@gmail.com> 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
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.johnston@gmail.com> wrote:
On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed <imonikemohammed@gmail.com> 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.
On Fri, Nov 19, 2021 at 3:37 PM Abdul Mohammed <imonikemohammed@gmail.com> wrote:
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.
A table in that context is something you create using the "Create table" command. And, per my other comments, you have to have done this before even creating the function in question since the function has to point to the table during the "create function" command's execution.
David J.
Hi,
Just one question
Isn't it possible to write a function that return an oid?
So.. One can write a function which create a temp table, feed it according to parameters and then return the oid of the created temp table.
No??
Le ven. 19 nov. 2021 à 23:48, David G. Johnston <david.g.johnston@gmail.com> a écrit :
On Fri, Nov 19, 2021 at 3:37 PM Abdul Mohammed <imonikemohammed@gmail.com> wrote: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.A table in that context is something you create using the "Create table" command. And, per my other comments, you have to have done this before even creating the function in question since the function has to point to the table during the "create function" command's execution.David J.
You could also just return the name of the temp table, or return nothing and directly use that name manually outside the function to do select * from pg_temp.my_data