Datatype error making PostgreSQL query from matlab - Stack Overflow

admin管理员组

文章数量:1122832

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.

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.

• postgresql
• matlab
• postgresql-14

Share Improve this question Follow edited Nov 26, 2024 at 16:00 Nate Anderson asked Nov 22, 2024 at 20:43 Nate AndersonNate Anderson 86722 gold badges1010 silver badges2727 bronze badges 1

• 1 I'm going to say this no converter found for element oid 1700 is coming from octave as I don't see uuid as one of the built in types. The only I can think of at this time is have public.fn_func() take a text argument and then do the cast to uuid in the function. – Adrian Klaver Commented Nov 22, 2024 at 23:44

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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

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.

本文标签： Datatype error making PostgreSQL query from matlabStack Overflow