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)
