On 5/15/20 5:16 PM, David G. Johnston
wrote:
Thank you for the confirmation. I'll decide whether I move to plpgsql or dither with role/search_path in the db creation scripts.On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsargent@xxxxxxxxx> 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.
(Both forms, plpgsql and sql, "work" once given the correct context.)
David J.
