Re: schema agnostic functions in language sql

On 5/15/20 5:41 PM, Tom Lane wrote:
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> 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.
> I don't think that's accurate. SQL functions are stored as plain text,
> just like any other non-C-coded function, and they are not parsed until
> execution.
>
> There are big differences from plpgsql of course. For one, it's
> possible for a SQL function to be "inlined" into the calling query,
> in which case parsing happens during planning of the calling query.
> But other than that, I'd expect the execution-time search path
> to determine how a SQL function behaves.
>
> Since Rob didn't provide any details, it's far from clear what's
> going wrong for him.
>
> regards, tom lane
Did my message with a sql and plgpsql versions not come through?

I cannot create a plain sql function unless the search_path covers any
table mentioned. Not the case when using plpgsql - no path needed.

I'm ok(ish) with that, unless I've missed some detail.

rjs