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 1rNGYa-00AXzN-65 for pgsql-sql@arkaria.postgresql.org; Tue, 09 Jan 2024 18:10:32 +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 1rNGXb-00Eoni-1K for pgsql-sql@arkaria.postgresql.org; Tue, 09 Jan 2024 18:09:31 +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 1rNGXa-00EonZ-Kk for pgsql-sql@lists.postgresql.org; Tue, 09 Jan 2024 18:09:30 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rNGXU-000gnn-3y for pgsql-sql@lists.postgresql.org; Tue, 09 Jan 2024 18:09:30 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-28c7c9b19f1so1768050a91.1 for ; Tue, 09 Jan 2024 10:09:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1704823762; x=1705428562; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=5vQxBYbNQ5WaqYBEciioizjTDxuktUlcLqsel69w2aY=; b=DgxOQAxLrsL6r2n3BxOQWWgGYFAQ/iLr4gWOg4NOYqcCjxawK9OKxQD7u225W9ADgF H7uLrinQDW+Z8S6b4RM9ugOBcuuCkQmnxv3/qoVaV4smLDgsJLZR7Pm7GfmujE+4y5Bp 6CKifhXk0mPkMO1ka4IfvNZGss2lWl+bddAn4m+bMBPxTRgt/lfmmtnik+CVnjHsi5VL 2hOq4FHS0WG8xUa2HHzIK6d7uPYwFrYxlrCMBDFZPgagIIeGHxkq/tI9jGOb+vHFyC2K npSJvYX6rCdo+i7UZZhFvG/qMjDH4k6BrhHrultxE90WgGbh17pXv8zcDWjz2l0RCUGX f3RA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1704823762; x=1705428562; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=5vQxBYbNQ5WaqYBEciioizjTDxuktUlcLqsel69w2aY=; b=EyfFuNg0KjUqi5Hmdp3bm5YJoIrPUjYHmOgHtInvIKbbNnJCD+Leh+mfRwfjVuXwah Vsv5wieT5wSSe5uaQGN69L2I3WScm08+RBwse/w5HmidwvDsJ1GoAAcFnt2JRuqpbO1c oxw1WDnb/hicKoZuU+rl1mdmhSdmMovOF7j9JCBHpKEsSo4a9IH9cNEv4HSxIPkiO7aQ l42LLgnWYLi5+i1fXWL1CyqA13cSndjH4soB/av5qajbSgEld29xorHZsBNBjyjVZJqD orkJ53Yu8lhWsA8MJB2XsoD3bLWrpuHEiaKFFl2tKSk6ywriuQiD9mRDrMW8laxjJ0XF EqdQ== X-Gm-Message-State: AOJu0Yz0OnuqkCOo+eh09/c90WcVObZJlgb/KtMWPFdCGZ1+mXBD7opY DnC83BSzHyUfCEYYIXnckMgLYuZ3XqNtpCbXKl8= X-Google-Smtp-Source: AGHT+IGNbVH6ZuGvFlTatKfbZKgNW7N4MRMDyCgTDHnMMVE5jBlHqh7z3aOupRyETCazzlImvd5qPk2Be+r0634eDyU= X-Received: by 2002:a17:90b:e06:b0:28b:cc27:8bb with SMTP id ge6-20020a17090b0e0600b0028bcc2708bbmr2535037pjb.47.1704823761968; Tue, 09 Jan 2024 10:09:21 -0800 (PST) MIME-Version: 1.0 References: <5d61c61ce2574b969895d5a4b6ba2602@express-scripts.com> In-Reply-To: <5d61c61ce2574b969895d5a4b6ba2602@express-scripts.com> From: Bindra Bambharoliya Date: Tue, 9 Jan 2024 23:39:09 +0530 Message-ID: Subject: Re: How to use one function which can be accessed for all schemas To: "Wetmore, Matthew (CTR)" Cc: intmail01 , pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000bca4fb060e873654" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bca4fb060e873654 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Create function in public or catalog schema. This function will be visible to each schema and user On Tue, 9 Jan 2024, 22:10 Wetmore, Matthew (CTR), < Matthew.Wetmore@express-scripts.com> wrote: > Schema qualify your function and trigger names. Schema qualify > everything, it=E2=80=99s good practice and doesn=E2=80=99t need to rely o= n search_path. > > This error say exactly that. > > > > CREATE FUNCTION test(=E2=80=A6 > > > > CREATE FUNCTION myschema.test( > > > > *From:* intmail01 > *Sent:* Tuesday, January 9, 2024 12:15 AM > *To:* pgsql-sql@lists.postgresql.org > *Subject:* [EXTERNAL] How to use one function which can be accessed for > all schemas > > > > Hi, > > > > I have several schemas in my database, I want to create just one function > to use with all these schema. > > I create the function in a main schema then the trigger call the function > and error occurs : "No function matches the given name and argument types= . > You might need to add explicit type casts." > > > > How to use just one function which can be work amongst all shemas in the > db ? > > My objective is to reduce update of functions code just once not for many > schemas. I dont want to duplicate my functions for each schema. > > > > Thanks > --000000000000bca4fb060e873654 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Create function in public or catalog schema. This functio= n will be visible to each schema and user

On Tue, 9 Jan 2024, 22:10 Wetmore,= Matthew (CTR), <= Matthew.Wetmore@express-scripts.com> wrote:

Schema qualify your function and trig= ger names.=C2=A0 Schema qualify everything, it=E2=80=99s good practice and = doesn=E2=80=99t need to rely on search_path.

This error say exactly that.

=C2=A0

CREATE FUNCTION test(=E2=80=A6=

=C2=A0

CREATE FUNCTION myschema.test(=

=C2=A0

From: intmail01 <intmail01@gmail= .com>
Sent: Tuesday, January 9, 2024 12:15 AM
To: pgsql-sql@lists.postgresql.org
Subject: [EXTERNAL] How to use one function which can be accessed fo= r all schemas

=C2=A0

Hi,

=C2=A0

I have several schemas in my database, I want to cre= ate just one function to use with all these schema.

I create the function in a main schema then the trig= ger call the function and error occurs : "No function matches the give= n name and argument types. You might need to add explicit type casts."=

=C2=A0

How to use just one function which can=C2=A0 be work= amongst all shemas in the db ?

My objective is to reduce update of functions code j= ust once not for many schemas. I dont want to duplicate my functions for ea= ch schema.

=C2=A0

Thanks

--000000000000bca4fb060e873654--