How to fix issue uuid_generate_v1() does not exist in postgresql , actually i have the function under public schema

Question

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from da.test_get(cast(? as integer),cast(? as text),cast(? as text),cast(? as text),cast(? as text))]; nested exception is org.postgresql.util.PSQLException: ERROR: spiexceptions.UndefinedFunction: function uuid_generate_v1() does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Where: Traceback (most recent call last):

enter image description here

• Jeremy: da.test_get is a user function i write for our system, it will invoke the function uuid_generate_v1()
• CL.: And what is the source code of da.test_get?
• Jeremy: insert into tmp.xxx_list select x.id, x.circuit_id, uuid_generate_v1(), uuid_generate_v1() from circuit_ma x left join tmp.xxx_list y on x.id = y.panel_id and x.circuit_id = y.circuit_id where y.panel_id is null
• Jeremy: I did not write the schema name of uuid_generate_v1(), because it is under schema public, and schema public is in the search_path .

Answer 1

Apparently, the CREATE FUNCTION statement used a wrong search path. (This is not shown by \df.)

You can either tell the function to use the value in the environment, or set it explicitly to a correct value:

ALTER FUNCTION da.test_get RESET search_path;

ALTER FUNCTION da.test_get SET search_path = da, public;

• Jeremy: Great idea, i will have a try. Actually this error in the system can not be replay every time. It happens by chance.

Answer 2

I faced similar problem and literally nothing worked for me... So I executed this in my custom schema as a workaround:

create function uuid_generate_v1() returns uuid
 strict
 parallel safe
 language plpgsql
as
$$
begin
 RETURN public.uuid_generate_v1();
end;
$$;

Now you are free to use this function just normally in your schema with no need to use public. prefix:

SELECT uuid_generate_v1()