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.