Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Datatype error making PostgreSQL query from matlab

I’m attempting to call a database function that takes a single UUID as a parameter. When I do this:

report_id = "5ed30f08-0de0-47f4-99e8-9aeeb8eb2dfe";

query = "select * from public.fn_func($1)";
report_nav = pq_exec_params (conn, query, {report_id});

I get this:

__pq_exec_params__: fatal error: ERROR:  function public.fn_func(text) does not exist

So clearly, something is converting report_id to the PostgreSQL text data type. So I tried to cast to a UUID by changing the query to this:

query = "select * public.fn_func($1::uuid)";

But then I get this message:

no converter found for element oid 1700

I’ve definitely added the uuid-ossp module to my database, so I can run this directly against the database:

select '5ed30f08-0de0-47f4-99e8-9aeeb8eb2dfe'::uuid;

I’m using octave v6.4 to execute the script, running on Ubuntu 22.04 with PostreSQL v14.13.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue I thought I had with generating a uuid from my octave script was a red herring. Within the the function I was calling, I'm doing extract(epoch from tts.timestampt); it was the numeric value returned by postgres that was actually giving octave trouble. Changing that data type allowed my function to execute as expected with a uuid parameter.


  2. 0

    Assigning to your local variable report_id makes the UUID literal type text effectively.

    Your second attempt is missing a FROM:

    query = "SELECT * FROM public.fn_func($1::uuid)";

    But you should really get this error:

    ERROR: syntax error at or near "public"

    If that does not fix it, then I am not sure how your client manages to thwart the query.

    You could create an (overloaded?) function taking text and cast in the function (like Adrian commented), or you could concatenate the query string with an untyped literal:

    query = "SELECT * FROM public.fn_func('5ed30f08-0de0-47f4-99e8-9aeeb8eb2dfe')";

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search