On 5/15/20 5:16 PM, David G. Johnston 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.)
