Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGJP6-00Ef6G-UF for pgsql-general@arkaria.postgresql.org; Thu, 21 Dec 2023 13:48:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rGJP5-009WvQ-Kt for pgsql-general@arkaria.postgresql.org; Thu, 21 Dec 2023 13:47:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGJP5-009WvH-9a for pgsql-general@lists.postgresql.org; Thu, 21 Dec 2023 13:47:59 +0000 Received: from mail.eclipso.de ([217.69.254.104]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGJP2-00DBK3-MH for pgsql-general@lists.postgresql.org; Thu, 21 Dec 2023 13:47:58 +0000 Received: from mail.eclipso.de (www1.eclipso.de [217.69.254.102]) by mail.eclipso.de with ESMTP id 52E48A64 for ; Thu, 21 Dec 2023 14:47:56 +0100 (CET) Date: Thu, 21 Dec 2023 14:47:55 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eclipso.de; s=mail; t=1703166476; bh=7M2pzOMh8ukLqKfRf+5EWk5PAYmMy9tst8JuVrU9jxE=; h=Date:From:Subject:Reply-To:To:References:From; b=r2W/lUenoAcfnTN5TzQCLABHlLbITup9DL9lJLVXYo9llxMz48ud0hm2MYbOhWYTD mV8IkK1myvLOvpXcWS9ql+yYQDMjo5U56pBqEu6E/AnCTKUjBUMuaN5AhCOsYUL/j9 SSRmVSbVLYzB+3EQTOynfsjqa0MV+h1OijG+v0RWMATCJegCFuO4FXieVZdbz+7M8H AezCpNLiUKbkN4GRmJ0nyCzYpXYsx53GA9wsP/X0oBfQ95oN+ajMDoZX1JDSSNd1tt nqhiC2XkPIU9l8sJ8PNz7n/qzJVS1mCI6PI0iMwzmxRnXWebu07khNXLjKgBRNtkfI 0LtQQJA1eBM7g== MIME-Version: 1.0 Message-ID: <07aac10034aaf87697ae401475f48909@mail.eclipso.de> X-Mailer: eclipso / 7.4.0 From: "Wilma Wantren" Subject: Changing a schema's name with function1 calling function2 Reply-To: "Wilma Wantren" To: References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Now as text mail, sorry. I accidentally posted the following to the bugs mailing list first. Sorry f= or this, now as a question here. The company I work for used to only use Oracle databases for their program = and now also supports PostgreSQL. With Postgres, we create a database, a user and a schema, which all have th= e same name. The name is chosen by the customers. Sometimes, the customers would like to export the data and definitions and = import them under a new name. This works well by using the -O (no owner) option for pg_dump/pg_restore an= d only exporting and importing the schema. After the import the schema is r= enamed. So far so simple and easy! But there is one problem: we have a database function function1, which call= s another function function2. To ensure that the function call is safe, we = set a search_path for function1. Both functions are created in the main schema (the one that is named by the= customer), the search_path is therefore set to this schema: ALTER FUNCTION function1 SET SEARCH_PATH TO Since the search_path of the function is not renamed when the schema is ren= amed, I need to know that there is such a search_path, which I then manuall= y change to the new schema name. Would it be possible that there is a variable that designates the schema in= which a function is located? Like this, for example: ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA; Since $FUNCTION_SCHEMA would change to denote the new schema when renaming = the schema I wouldn't have to change the search_path manually. ________________________________________________________ Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.ecl= ipso.de