Subject: How to use one function which can be accessed for all schemas

From: intmail01 <[email-masked]>
Date: Tue, 9 Jan 2024 11:15:17 +0300

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 09 Jan 2024 10:29:56 -0500

> You might need to add explicit type casts."

You can either explicitly schema-qualify the function call, or make
sure that that main schema is always present in your search_path.

			regards, tom lane

---

From: "Wetmore, Matthew (CTR)" <[email-masked]>
Date: Tue, 9 Jan 2024 16:39:58 +0000

Schema qualify your function and trigger names.  Schema qualify everything, it’s good practice and doesn’t need to rely on search_path.

This error say exactly that.

CREATE FUNCTION test(…

CREATE FUNCTION myschema.test(

From: intmail01 <[email-masked]>
Sent: Tuesday, January 9, 2024 12:15 AM
To: [email-masked]
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

---

From: Bindra Bambharoliya <[email-masked]>
Date: Tue, 9 Jan 2024 23:39:09 +0530

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), <
[email-masked]> wrote:

> Schema qualify your function and trigger names.  Schema qualify
> everything, it’s good practice and doesn’t need to rely on search_path.
>
> *From:* intmail01 <[email-masked]>
> *Sent:* Tuesday, January 9, 2024 12:15 AM
> *To:* [email-masked]
> *Subject:* [EXTERNAL] How to use one function which can be accessed for
> all schemas
>
>
>
> Hi,
>
>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 9 Jan 2024 11:38:31 -0700

> Create function in public or catalog schema. This function will be visible

Schemas don't have their own visibility, you should not create stuff in
pg_catalog, and public is not guaranteed to be in the search_path.

And, please do not top-post replies.

David J.