Re: 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(dot)klaver(at)aklaver(dot)com