On 5/15/20 5:16 PM, David G. Johnston wrote:
> On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)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.
>