Re: schema agnostic functions in language sql - Mailing list pgsql-general
From | Rob Sargent |
---|---|
Subject | Re: schema agnostic functions in language sql |
Date | |
Msg-id | 92aa43c7-6b96-ab7f-ddde-61346f957801@gmail.com Whole thread Raw |
In response to | Re: schema agnostic functions in language sql (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: schema agnostic functions in language sql
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
List | pgsql-general |
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 >> >> > >
pgsql-general by date: