Thread: 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 functions. The 'parser'? does not recognize the tables (sans schema qualifier):
I've found that I can create the function, in public, if I set the search_path containing one schema (of course) and then successfully access the function after resetting the search path to use a second schema. My "build the world" scripting has so far avoided needing to know/use any specific role. Another pipe dream vaporized?
Thanks,
rjs
ERROR: relation "<tablename>" does not exist.I would rather not have to duplicate these across multiple schema - I'll use plpgsql instead unless I've overlooked some other avenue.
I've found that I can create the function, in public, if I set the search_path containing one schema (of course) and then successfully access the function after resetting the search path to use a second schema. My "build the world" scripting has so far avoided needing to know/use any specific role. Another pipe dream vaporized?
Thanks,
rjs
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 does not appear to be true
> for plain sql functions. The 'parser'? does not recognize the tables
> (sans schema qualifier):
>
> ERROR: relation "<tablename>" does not exist.
You probably need to show example code, because I don't see this:
show search_path ;
search_path
------------------------------------------------------------------
public,accounting,history,main,utility,timeclock,table_templates
\d utility.login_info
Table "utility.login_info"
Column | Type | Collation | Nullable |
Default
-------------+--------------------------------+-----------+----------+------------------
user_name | character varying | | not null |
user_pwd | character varying | | not null |
user_role | character varying | | |
ts_insert | timestamp(0) without time zone | | |
now()
ts_update | timestamp(0) without time zone | | |
user_update | character varying(20) | | |
user_insert | character varying(20) | | |
"session_user"()
CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
SELECT * FROM login_info WHERE user_name = $1;
$$ LANGUAGE SQL;
select * from getli('aklaver');
user_name | user_pwd | user_role | ts_insert |
ts_update | user_update | user_insert
-----------+----------+-----------+---------------------+---------------------+-------------+-------------
aklaver | ranger | | 12/29/2012 12:23:17 | 05/15/2020
15:41:14 | | postgres
(1 row)
>
> I would rather not have to duplicate these across multiple schema - I'll
> use plpgsql instead unless I've overlooked some other avenue.
>
> I've found that I can create the function, in public, if I set the
> search_path containing one schema (of course) and then successfully
> access the function after resetting the search path to use a second
> schema. My "build the world" scripting has so far avoided needing to
> know/use any specific role. Another pipe dream vaporized?
>
> Thanks,
> rjs
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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 correct schema-dependent table. The same does not appear to
>> be true for plain sql functions. The 'parser'? does not recognize
>> the tables (sans schema qualifier):
>>
>> ERROR: relation "<tablename>" does not exist.
>
> You probably need to show example code, because I don't see this:
>
> show search_path ;
> search_path
> ------------------------------------------------------------------
> public,accounting,history,main,utility,timeclock,table_templates
>
> \d utility.login_info
> Table "utility.login_info"
> Column | Type | Collation | Nullable |
> Default
> -------------+--------------------------------+-----------+----------+------------------
>
> user_name | character varying | | not null |
> user_pwd | character varying | | not null |
> user_role | character varying | | |
> ts_insert | timestamp(0) without time zone | | | now()
> ts_update | timestamp(0) without time zone | | |
> user_update | character varying(20) | | |
> user_insert | character varying(20) | | |
> "session_user"()
>
>
> CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
> SELECT * FROM login_info WHERE user_name = $1;
> $$ LANGUAGE SQL;
>
> select * from getli('aklaver');
> user_name | user_pwd | user_role | ts_insert |
> ts_update | user_update | user_insert
> -----------+----------+-----------+---------------------+---------------------+-------------+-------------
>
> aklaver | ranger | | 12/29/2012 12:23:17 | 05/15/2020
> 15:41:14 | | postgres
> (1 row)
>
Isn't "utility" in your path above?
>
>>
>> I would rather not have to duplicate these across multiple schema -
>> I'll use plpgsql instead unless I've overlooked some other avenue.
>>
>> I've found that I can create the function, in public, if I set the
>> search_path containing one schema (of course) and then successfully
>> access the function after resetting the search path to use a second
>> schema. My "build the world" scripting has so far avoided needing to
>> know/use any specific role. Another pipe dream vaporized?
>>
>> Thanks,
>> rjs
>>
>>
>
>
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 let the runtime search_path
>>> find the correct schema-dependent table. The same does not appear to
>>> be true for plain sql functions. The 'parser'? does not recognize
>>> the tables (sans schema qualifier):
>>>
>>> ERROR: relation "<tablename>" does not exist.
>>
>> You probably need to show example code, because I don't see this:
>>
>> show search_path ;
>> search_path
>> ------------------------------------------------------------------
>> public,accounting,history,main,utility,timeclock,table_templates
>>
>> \d utility.login_info
>> Table "utility.login_info"
>> Column | Type | Collation | Nullable |
>> Default
>> -------------+--------------------------------+-----------+----------+------------------
>>
>> user_name | character varying | | not null |
>> user_pwd | character varying | | not null |
>> user_role | character varying | | |
>> ts_insert | timestamp(0) without time zone | | | now()
>> ts_update | timestamp(0) without time zone | | |
>> user_update | character varying(20) | | |
>> user_insert | character varying(20) | | |
>> "session_user"()
>>
>>
>> CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
>> SELECT * FROM login_info WHERE user_name = $1;
>> $$ LANGUAGE SQL;
>>
>> select * from getli('aklaver');
>> user_name | user_pwd | user_role | ts_insert |
>> ts_update | user_update | user_insert
>> -----------+----------+-----------+---------------------+---------------------+-------------+-------------
>>
>> aklaver | ranger | | 12/29/2012 12:23:17 | 05/15/2020
>> 15:41:14 | | postgres
>> (1 row)
>>
> Isn't "utility" in your path above?
Yes. In your OP you had:
"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 functions."
I was showing that search_path works with SQL functions, which you
indicated was not happening for you.
Are you talking about some other case?
>>
>>>
>>> I would rather not have to duplicate these across multiple schema -
>>> I'll use plpgsql instead unless I've overlooked some other avenue.
>>>
>>> I've found that I can create the function, in public, if I set the
>>> search_path containing one schema (of course) and then successfully
>>> access the function after resetting the search path to use a second
>>> schema. My "build the world" scripting has so far avoided needing to
>>> know/use any specific role. Another pipe dream vaporized?
>>>
>>> Thanks,
>>> rjs
>>>
>>>
>>
>>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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
>>>> 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 functions. The 'parser'? does
>>>> not recognize the tables (sans schema qualifier):
>>>>
>>>> ERROR: relation "<tablename>" does not exist.
>>>
>>> You probably need to show example code, because I don't see this:
>>>
>>> show search_path ;
>>> search_path
>>> ------------------------------------------------------------------
>>> public,accounting,history,main,utility,timeclock,table_templates
>>>
>>> \d utility.login_info
>>> Table "utility.login_info"
>>> Column | Type | Collation | Nullable
>>> | Default
>>> -------------+--------------------------------+-----------+----------+------------------
>>>
>>> user_name | character varying | | not null |
>>> user_pwd | character varying | | not null |
>>> user_role | character varying | | |
>>> ts_insert | timestamp(0) without time zone | | | now()
>>> ts_update | timestamp(0) without time zone | | |
>>> user_update | character varying(20) | | |
>>> user_insert | character varying(20) | | |
>>> "session_user"()
>>>
>>>
>>> CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
>>> SELECT * FROM login_info WHERE user_name = $1;
>>> $$ LANGUAGE SQL;
>>>
>>> select * from getli('aklaver');
>>> user_name | user_pwd | user_role | ts_insert | ts_update
>>> | user_update | user_insert
>>> -----------+----------+-----------+---------------------+---------------------+-------------+-------------
>>>
>>> aklaver | ranger | | 12/29/2012 12:23:17 | 05/15/2020
>>> 15:41:14 | | postgres
>>> (1 row)
>>>
>> Isn't "utility" in your path above?
>
> Yes. In your OP you had:
>
> "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 functions."
>
> I was showing that search_path works with SQL functions, which you
> indicated was not happening for you.
>
> Are you talking about some other case?
>
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.
sarge=# \dn
List of schemas
Name | Owner
--------+----------
base | postgres
bulk | postgres
gt | postgres
public | postgres
sss | postgres
(5 rows)
sarge=# show search_path;
search_path
-----------------
"$user", public
(1 row)
sarge=#
sarge=# create or replace function public.segment_calls(segid uuid)
returns table (name text, firsti int, lasti int, calls text)
as
$$
select
p.name,
s.firstmarker,
s.lastmarker,
regexp_replace(substr(g.calls,1+(2*s.firstmarker),
2*(s.lastmarker-s.firstmarker+1))::text, '(..)', ' \1','g') as calls
from
segment s
join probandset b on s.probandset_id = b.id
join people l on b.people_id = l.id
join people_member m on l.id = m.people_id
join person p on m.person_id = p.id
join genotype g on g.markerset_id = s.markerset_id and g.person_id
= p.id
where s.id = segid;
$$
language sql
;
sarge-# ERROR: relation "segment" does not exist
LINE 11: segment s
^
sarge=# create or replace function public.segment_calls(segid uuid)
returns table (name text, firsti int, lasti int, calls text)
as
$$
begin
select
p.name,
s.firstmarker,
s.lastmarker,
regexp_replace(substr(g.calls,1+(2*s.firstmarker),
2*(s.lastmarker-s.firstmarker+1))::text, '(..)', ' \1','g') as calls
from
segment s
join probandset b on s.probandset_id = b.id
join people l on b.people_id = l.id
join people_member m on l.id = m.people_id
join person p on m.person_id = p.id
join genotype g on g.markerset_id = s.markerset_id and g.person_id
= p.id
where s.id = segid;
end;
$$
language plpgsql;
sarge-# CREATE FUNCTION
sarge=# Query buffer reset (cleared).
sarge=# \dt gt.*
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
gt | chaseable | table | postgres
gt | duo_chaseable | table | postgres
gt | genotype | table | postgres
gt | ld | table | postgres
gt | probandset | table | postgres
gt | probandset_group | table | postgres
gt | probandset_group_member | table | postgres
gt | process | table | postgres
gt | process_arg | table | postgres
gt | process_input | table | postgres
gt | process_output | table | postgres
gt | projectfile | table | postgres
gt | segment | table | postgres
gt | segmentset | table | postgres
gt | threshold | table | postgres
gt | threshold_duo_segment | table | postgres
gt | threshold_segment | table | postgres
(17 rows)
On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsargent@gmail.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.
David J.
On 5/15/20 5:16 PM, David G. Johnston wrote:
Thank you for the confirmation. I'll decide whether I move to plpgsql or dither with role/search_path in the db creation scripts.On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsargent@gmail.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.
(Both forms, plpgsql and sql, "work" once given the correct context.)
David J.
"David G. Johnston" <david.g.johnston@gmail.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
On 5/15/20 5:41 PM, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.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
On 5/15/20 4:58 PM, Rob Sargent wrote: > > > On 5/15/20 5:41 PM, Tom Lane wrote: >> "David G. Johnston" <david.g.johnston@gmail.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.klaver@aklaver.com
Rob Sargent <robjsargent@gmail.com> 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 default we'll try to parse and analyze the body, so
any unknown tables will draw an error. plpgsql doesn't go further than a
very crude syntax check.
If you don't like that, you can set check_function_bodies = off while
creating your SQL functions. But in any case, it's only related to what
happens at execution if the search path is the same.
regards, tom lane
> 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() 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 > Yes the plpgsql form works but of course requires a sufficient search_path > > -- > Adrian Klaver > adrian.klaver@aklaver.com
> On May 15, 2020, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Rob Sargent <robjsargent@gmail.com> 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 default we'll try to parse and analyze the body, so > any unknown tables will draw an error. plpgsql doesn't go further than a > very crude syntax check. > > If you don't like that, you can set check_function_bodies = off while > creating your SQL functions. But in any case, it's only related to what > happens at execution if the search path is the same. > > regards, tom lane And my fundamental error was thinking the parse of all create function calls was not language specific beyond syntax. Lookingback, my use of sql functions has been for inline-able calculations reused in other plpgsql functions. check_function_body=off may be what I want during the site install as the definitions should be correct in all aspects. Thank you all rjs
On Sat, May 16, 2020 at 5:15 AM Rob Sargent <robjsargent@gmail.com> 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 search_path.
David J.
On May 16, 2020, at 9:13 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
In fact there is just such a thing in the database from which get the DDL to generate new ‘client’ dbs. (This is not commercial in any way). So far I have not needed to bring that template into client space. Do you see a problem with using check_ function_body=off, given that the functions will be developed and tested elsewhere?On Sat, May 16, 2020 at 5:15 AM Rob Sargent <robjsargent@gmail.com> 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 search_path.David J.