From: | DAVID ROTH <adaptron(at)comcast(dot)net> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | General Performance Question |
Date: | 2021-11-18 14:15:13 |
Message-ID: | 1653563957.563818.1637244913291@connect.xfinity.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2021-11-18 14:27:53 | Re: General Performance Question |
Previous Message | Pavel Stehule | 2021-11-18 11:43:36 | Re: Execute command in PL/pgSQL function not executing |