Re: schema agnostic functions in language sql - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: schema agnostic functions in language sql |
Date | |
Msg-id | a15a707a-1c33-8d23-6133-ff1466234700@aklaver.com Whole thread Raw |
In response to | schema agnostic functions in language sql (Rob Sargent <robjsargent@gmail.com>) |
Responses |
Re: schema agnostic functions in language sql
(Rob Sargent <robjsargent@gmail.com>)
|
List | pgsql-general |
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.klaver@aklaver.com
pgsql-general by date: