I mean it surprised me, but there are many ways around that. May be less efficient, but you can always use string-to-array, or json, or copy more for CTE then work with inputs as a table.
Based on old memories since I’ve been working in mongo lately, after making the UDT on the db side, you make a data table that has the same name, namespace (ie dbo/public), and the same schema as the UDT (better if that could be generated) and populate it in code. Then you execute the db query with the UDT type as a parameter.
This is better for a few reasons, including not building up a string, but also having the same text means that each query didn’t need to be re-parsed and can reuse execution plans. If the query text isn’t an exact match, it gets that whole pipeline each time.
I learned that not too long ago, too.
I mean it surprised me, but there are many ways around that. May be less efficient, but you can always use string-to-array, or json, or copy more for CTE then work with inputs as a table.
Create a user defined table type and use that as a parameter. I’m not sure what the postgres name of that is.
And how do you put data into the table?
Based on old memories since I’ve been working in mongo lately, after making the UDT on the db side, you make a data table that has the same name, namespace (ie dbo/public), and the same schema as the UDT (better if that could be generated) and populate it in code. Then you execute the db query with the UDT type as a parameter.
This is better for a few reasons, including not building up a string, but also having the same text means that each query didn’t need to be re-parsed and can reuse execution plans. If the query text isn’t an exact match, it gets that whole pipeline each time.