Generate the same type for queries with the same SELECT ... FROM

[mbark]

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 entity CostCenter that is stored in the cost_center table.

Then we have several different ways of querying for the CostCenter (FindByID, FindBySubscriber, ListBySubscribers etc). All of these queries always do SELECT * FROM cost_center and then do the same mapping from the returned database type to our internal rpc 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):

	FindByID(ctx context.Context, costCenterID db.UUID) (FindByIDRow, error)
	FindBySubscriber(ctx context.Context, subscriberID db.UUID) (FindBySubscriberRow, error)
	ListBySubscribers(ctx context.Context, subscriberIDs pgtype.UUIDArray) ([]ListBySubscribersRow, error)

But FindByIDRow, FindBySubscriberRow and ListBySubscribersRow are all identical structs (except for the name).

What would be nice is something like:

	FindByID(ctx context.Context, costCenterID db.UUID) (SelectCostCenterRow, error)
	FindBySubscriber(ctx context.Context, subscriberID db.UUID) (SelectCostCenterRow, error)
	ListBySubscribers(ctx context.Context, subscriberIDs pgtype.UUIDArray) ([]SelectCostCenterRow, error)

[jschaf]

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:

• A query may optionally declare the output type name.
• Other queries may also declare the same output type name.
• For queries that declare the same output types, the output column names and types must exactly match. The order of columns may differ.

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;

[mbark]

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.

[jschaf]

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.

[kirk-anchor]

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.

[bweston92]

@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?

[kirk-anchor]

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.