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