Agreed. The code I am migrating uses the EXECUTE immediate form for almost everything. The path of least resistance for me is to 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. I am also hoping to get a better understanding of the way the Postgres optimizer works. Any links you could suggest for this would be appreciated. > On 11/18/2021 9:27 AM Thomas Kellerer <shammat@xxxxxxx> 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.
