On 12/27/24 12:26, David G. Johnston wrote:
On Friday, December 27, 2024, Jan Behrens <jbe-mlist@xxxxxxxxxxxxx
<mailto:jbe-mlist@xxxxxxxxxxxxx>> wrote:
It seems that it matters *both* how the search_path was set during
the *first* invocation of the function within a session *and* how it
is set during the actual call of the function. So even if there are
just two schemas involved, there are 4 possible outcomes for the
"run" function's result ('2.4', '2', '5', and '5.4'). To me, this
behavior seems to be somewhat dangerous. Maybe it is even considered
a bug?
It is what it is - and if one is not careful one can end up writing
hard-to-understand and possibly buggy code due to the various execution
environments and caches involved.
I’ve never really understood why “%TYPE’ exists…
Per:
https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE
"By using %TYPE you don't need to know the data type of the structure
you are referencing, and most importantly, if the data type of the
referenced item changes in the future (for instance: you change the type
of user_id from integer to real), you might not need to change your
function definition.
%TYPE is particularly valuable in polymorphic functions, since the data
types needed for internal variables can change from one call to the
next. Appropriate variables can be created by applying %TYPE to the
function's arguments or result placeholders."
The second case I can buy, the first I am not so sure of. It seems to me
the first case it can be 'solved' by the second case.
Or is it documented somewhere?
https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING <https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING>
Can someone explain to me what's going on, and what is the best
practice to deal with it? Is there a way to avoid fully qualifying
every type and expression? Which parts do I have to qualify or is
this something that could be fixed in a future version of PostgreSQL?
Add qualification or attach a “set search_path” clause to “create
function”. Code stored in the server should not rely on the session
search_path.
David J.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
