Open mbark opened 2 years ago
Thanks for the solid write up. I also want this functionality. Technically, it’s not terribly challenging. The main design problem is how to handle edge cases. I think behavior is something like:
As a workaround until this is landed you can use cleverness with SQL predicates to use a query for multiple query patterns. Here’s one we use
-- FindItems finds Items.
-- name: FindItems :many
SELECT
name,
vendor_names,
default_price
FROM item_api
WHERE tenant_id = simc37_int(pggen.arg('tenant_id'))
AND (
simc.is_wildcard(pggen.arg('item_id'))
OR item_id = simc.default_current_resource_id(pggen.arg('item_id'))
)
AND temporal.select_partition(pggen.arg('partition'), asr)
AND (pggen.arg('filter') = '' OR categories ~ (pggen.arg('filter') || '.*')::lquery)
ORDER BY item_id, lower(asr) DESC;
If you could point me in the right direction (and if you are interested) @jschaf then I could take a shot at writing a PR with this functionality.
Is your suggested solution that you would be able to (optionally) write:
-- name: FindItems :many name: ItemRow
And then have that query generate ItemRow
as a result. Checking during / after generation that all queries using the same column do indeed result in the same row.
If you could point me in the right direction (and if you are interested) @jschaf then I could take a shot at writing a PR with this functionality.
Sure, I'm happy to take a look at PRs and either merge after code review or combine with some edits from me (giving you author credit).
For syntax, maybe:
-- name: FindItems :many output=ItemRow
I think going forward, key=value is the clearest syntax. I cargoculted the syntax from sqlc but we're in new territory with the out type param.
You can do this by using the composite type for the table. Instead of SELECT * FROM cost_center
, use SELECT cost_center FROM cost_center
. All your queries with SELECT cost_center
will return the same struct generated for the composite type of the table. It's also more robust since SELECT *
will have scan errors if you add a column to your table and you will have to regenerate and redeploy to fix it but SELECT cost_center
will still work because the query still returns 1 column and it will still be able to use the struct previously generated.
@kirk-anchor that doesn't seem to be the case for me, I have a select with the column names exactly the same in 2 queries and I get different structs.
@mbark did you get any working code for someone to continue with?
You have to select the composite type, SELECT my_table FROM my_table
, not the column names, SELECT my_column1, my_column2 FROM my_table
or use a custom defined Postgres TYPE.
First of all: thanks so much for this library! It's just incredible -- it feels exactly like how I want to work with databases in go.
Background
Most of our tables are representations of an
rpc
type, so that we have an entityCostCenter
that is stored in thecost_center
table.Then we have several different ways of querying for the
CostCenter
(FindByID
,FindBySubscriber
,ListBySubscribers
etc). All of these queries always doSELECT * FROM cost_center
and then do the same mapping from the returned database type to our internalrpc
type.This requires repeating the mapping from the pggen-generated row type to the
rpc
type for each query -- even though the structs have exactly the same structure.Suggestion
Add a way to have
SELECT
statements that return the exact same thing, also use the same type.e.g. currently we have this generated code (removing comments and batching for readability):
But
FindByIDRow
,FindBySubscriberRow
andListBySubscribersRow
are all identical structs (except for the name).What would be nice is something like: