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?
Thanks
Dave

Reply via email to