Re: schema agnostic functions in language sql

> On May 16, 2020, at 9:13 AM, David G. Johnston > wrote: > >  >> On Sat, May 16, 2020 at 5:15 AM Rob Sargent wrote: >> check_function_body=off may be what I want during the site install as the >> definitions should be correct in all aspects. > > You should probably just have one "test" s

Re: schema agnostic functions in language sql

On Sat, May 16, 2020 at 5:15 AM Rob Sargent wrote: > check_function_body=off may be what I want during the site install as the > definitions should be correct in all aspects. > You should probably just have one "test" schema and compile your functions with the non-client test schema in the searc

Re: schema agnostic functions in language sql

> On May 15, 2020, at 6:03 PM, Tom Lane wrote: > > Rob Sargent writes: >> I cannot create a plain sql function unless the search_path covers any >> table mentioned. Not the case when using plpgsql - no path needed. > > Oh, one of the things that's quite a lot different is the checking > ap

Re: schema agnostic functions in language sql

> On >>> 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() ru

Re: schema agnostic functions in language sql

Rob Sargent writes: > I cannot create a plain sql function unless the search_path covers any > table mentioned. Not the case when using plpgsql - no path needed. Oh, one of the things that's quite a lot different is the checking applied at function creation time ;-). For a SQL function, by defa

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" 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) a

Re: schema agnostic functions in language sql

On 5/15/20 5:41 PM, Tom Lane wrote: "David G. Johnston" 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

Re: schema agnostic functions in language sql

"David G. Johnston" 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 non

Re: schema agnostic functions in language sql

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

Re: schema agnostic functions in language sql

On Fri, May 15, 2020 at 4:07 PM Rob Sargent 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

Re: schema agnostic functions in language sql

On 5/15/20 4:58 PM, Adrian Klaver wrote: On 5/15/20 3:53 PM, Rob Sargent wrote: On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql fu

Re: schema agnostic functions in language sql

On 5/15/20 3:53 PM, Rob Sargent wrote: On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and

Re: schema agnostic functions in language sql

On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the

Re: schema agnostic functions in language sql

On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the correct schema-dependent table.  The same doe

schema agnostic functions in language sql

I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the correct schema-dependent table.  The same does not appear to be true for plain sql f