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

[mbark]

trafficstars

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.