I'm having some difficulty with inserting some data using libpq. I have two custom data types:
create type size as (width real, height real);
create type rotated_rect as (angle real, center point, bounding_box box, size size)
and I would like to insert a record into a table which has a rotated_rect field, so for the field using libpq I'm putting together the string value:
paramv[3] = "(10.5,10.1,10.2,20,20,20,40,(5,5))";
However, it's giving me the error: invalid input syntax for type point: "10.1"
I've also tried:
paramv[3] = "(10.5,(10.1,10.2),20,20,20,40,(5,5))"; -> invalid input syntax for "(10.1"
paramv[3] = "(10.5,(10.1,10.2),(20,20,20,40),(5,5))"; -> as above
and the sql command I'm using is:
res = PQexecParams(conn, "insert into test (r,b,s,rr) values ($1::real,$2::box,$3::size,$4::rotated_rect)", 4, NULL, paramv, NULL, NULL,0);
How do I fix this?
What got me were that escaped double quotes and parenthesis need to be used around the values representing a field of the custom compound data type which requires more than one value to create, so:
paramv[0] = "(10.5,\"(10.1,10.2)\",\"(20,20,20,40)\",\"(5,5)\")";
As this string is used as a parameter, the single quotes that would usually wrap the outer parenthesis are not needed.
In a non-parameterised query, it would be implemented like so with the single quotes:
res = PQexec(conn, "insert into test (rr) values ('(10.5,\"(10.1,10.2)\",\"(20,20,20,40)\",\"(5,5)\")')");