Thread: Composite types as parameters

Hello all,

I am trying to add support for composite types to my ORM, which uses libpq 
and the binary format.

Given a schema like this one:

create type composite as (...);
create table sometable (field composite, ...);

I want to execute a query like this:

PQexecParams("insert into sometable values($1, ...);", paramValues[0] = serialize some record, ...)

However this fails in coerce_record_to_complex(), because it receives a 
node of type Param, but it can only handle RowExpr and Var.  There is a 
comment suggesting that this is not a fundamental limitation, but (not 
being familiar with postgres codebase) I'm not sure how to go about fixing 
it.  I assume there is a mapping somewhere from param ids to objects, but 
am unable to find it.

Does anyone have any pointers or suggestions?  Am I going about this in 
entirely the wrong way?

  -E

Elijah Stone <elronnd@elronnd.net> writes:
> I want to execute a query like this:

> PQexecParams("insert into sometable values($1, ...);", paramValues[0] = serialize some record, ...)

> However this fails in coerce_record_to_complex(), because it receives a
> node of type Param, but it can only handle RowExpr and Var.

You probably would have better results from specifying the composite
type explicitly in the query:

PQexecParams("insert into sometable values($1::composite, ...);",

I gather from the complaint that you're currently doing something that
causes the Param to be typed as a generic "record", which is problematic
since the record's details are not available from anyplace.  But if you
cast it directly to a named composite type, that should work.

If it still doesn't work, please provide a more concrete example.

            regards, tom lane

On Sat, 26 Jun 2021, Tom Lane wrote:
> You probably would have better results from specifying the composite 
> type explicitly in the query:
>
> PQexecParams("insert into sometable values($1::composite, ...);",
>
> I gather from the complaint that you're currently doing something that 
> causes the Param to be typed as a generic "record", which is problematic 
> since the record's details are not available from anyplace.  But if you 
> cast it directly to a named composite type, that should work.
>
> If it still doesn't work, please provide a more concrete example.

Thanks, unfortunately adding the explicit cast doesn't help.  I've 
attached a minimal runnable example.

I am serializing as a generic record, so it occurs to me that another 
solution would be to use the actual type of the composite in question. 
(Though it also seems to me that my code should work as-is.)  Is there a 
way to discover the OID of a composite type?  And is the wire format the 
same as for a generic record?

  -E

Elijah Stone <elronnd@elronnd.net> writes:
> On Sat, 26 Jun 2021, Tom Lane wrote:
>> If it still doesn't work, please provide a more concrete example.

> Thanks, unfortunately adding the explicit cast doesn't help.  I've 
> attached a minimal runnable example.

So your problem is that you're explicitly saying that the input is
of generic-RECORD type.  You should let the server infer its type,
instead, which it can easily do from context in this example.
That is, pass zero as the type OID, or leave out the paramTypes
array altogether.  The example works for me with this change:

@@ -30,13 +30,13 @@
 
        // error:
        check(PQexecParams(c, "INSERT INTO tab VALUES($1, 8);",
-                       1, &(Oid){RECORDOID}, &(const char*){recbuf},
+                       1, &(Oid){0}, &(const char*){recbuf},
                        &(int){rec - recbuf}, &(int){1/*binary*/},
                        1/*binary result*/));
 
        // error as well:
        check(PQexecParams(c, "INSERT INTO tab VALUES($1::some_record, 8);",
-                       1, &(Oid){RECORDOID}, &(const char*){recbuf},
+                       1, &(Oid){0}, &(const char*){recbuf},
                        &(int){rec - recbuf}, &(int){1},
                        1));

In more complicated cases you might need to fetch the composite
type's actual OID and pass that.  But I'd go with the lazy approach
until forced to do differently.

> Is there a 
> way to discover the OID of a composite type?  And is the wire format the 
> same as for a generic record?

Same as for any other type: SELECT 'mytypename'::regtype::oid.
And yes.

            regards, tom lane

On Sun, 27 Jun 2021, Tom Lane wrote:

> You should let the server infer its type, instead, which it can easily 
> do from context in this example.  That is, pass zero as the type OID, or 
> leave out the paramTypes

Ah, thank you, that works brilliantly.  Sorry for the noise!

  -E