On 5/15/20 5:16 PM, David G. Johnston wrote:
On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsarg...@gmail.com
<mailto:robjsarg...@gmail.com>> wrote:
I'm terribly sorry: I needed to add that plpgsql works without any
knowledge of the schema, where as defining a plain sql functions does
not work without schema qualification.
You need to distinguish between "works" as in "compiles" and "works"
as in "executes".
Yes, SQL and pl/pgsql have very different behaviors when it comes to
compilation and execution. In particular SQL performs parsing earlier
(during creation - just like it does for views) and links the textual
query to its parse result earlier. For pl/pgsql none of that happens
until the function is called. Because of this pl/pgsql allows for
ambiguous sql text to exist and be concretely resolved during
execution while SQL does not.
Thank you for the confirmation. I'll decide whether I move to plpgsql
or dither with role/search_path in the db creation scripts.
(Both forms, plpgsql and sql, "work" once given the correct context.)
David J.
