3

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

5
  • And what is da.test_get?
    – CL.
    Commented Sep 11, 2018 at 9:03
  • da.test_get is a user function i write for our system, it will invoke the function uuid_generate_v1()
    – Jeremy
    Commented Sep 13, 2018 at 7:06
  • And what is the source code of da.test_get?
    – CL.
    Commented Sep 13, 2018 at 7:33
  • 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
    Commented Sep 13, 2018 at 11:35
  • 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 .
    – Jeremy
    Commented Sep 13, 2018 at 11:37

2 Answers 2

0

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;
1
  • Great idea, i will have a try. Actually this error in the system can not be replay every time. It happens by chance.
    – Jeremy
    Commented Sep 14, 2018 at 3:34
0

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.