Hi, thanks for your answer !
Sorry for being late...
I'm sure :-)
But I made more research and find the problem : the function I use...
I'm using the gen_random_uuid() function wich is included into postgresql core since v13 and was originally into the pgcrypto extension.
So when you don't prefix the function call, PostgreSQL don't use any prefix because this is a core function and there is no need for prefix...
When you prefix it, PostgreSQL keep the prefix in case you call another function (that you CREATE into another schema for exemple).
When you use a function that is stored into the public schema but is not part of the core (personnal functions or functions from extensions) PostgreSQL keep the prefix you add into the CREATE statement or add a prefix if you omit it regarding the place where the function is stored (depending of the search_path you have when you run the statement).
So, there is no problem it was a misunderstanding on my side on how pg_dump works with core function.
Maybe adding the "pg_catalog" prefix could be an improvment, I will propose that.
Thanks !
Best regards
Arthur Bazin
Le jeu. 31 août 2023 à 23:47, Erik Wienhold <ewie@xxxxxxxxx> a écrit :
> On 31/08/2023 17:08 CEST Arthur Bazin <arthurbazin@xxxxxxxxx> wrote:
>
> Consider that we have a function in the public schema witch is named
> my_function_in_public.
>
> In PG11 this table :
> CREATE TABLE public.test_dump (
> id TEXT DEFAULT my_function_in_public()
> );
> When you dump this table with the pg11 binaries, you obtain this script :
> CREATE TABLE public.test_dump (
> id TEXT DEFAULT public.my_function_in_public()
> );
> => the schema prefix have been added to the function by pg_dump.
>
> In PG13, the same table :
> CREATE TABLE public.test_dump (
> id TEXT DEFAULT my_function_in_public()
> );
> When you dump this table with the pg13 binaries, you obtain this script :
> CREATE TABLE public.test_dump (
> id TEXT DEFAULT my_function_in_public()
> );
> => the schema prefix have not been added.
Are you sure that my_function_in_public was created in schema public on pg13?
I cannot reproduce this on 13.12. However, I can reproduce it when creating
that function in pg_catalog instead of public. The dump does not include
pg_catalog.my_function_in_public though.
--
Erik
