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

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 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.klaver@aklaver.com