Re: schema agnostic functions in language sql

On 5/15/20 4:58 PM, Rob Sargent wrote:
>
>
> 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.

But does the plpgsql segment_calls() run?

On other words does:

select * from segment_calls(segid uuid);

work?

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com