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 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?

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:

On Fri, May 15, 2020 at 4:07 PM Rob Sargent <robjsargent@gmail.com
<mailto: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.

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 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

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. Looking back, 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:



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.

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?