Search Postgresql Archives

Re: schema agnostic functions in language sql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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










[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux