Re: schema agnostic functions in language sql - Mailing list pgsql-general

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)