Re: General Performance Question

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.