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