Closed
Description
A sqlc user asked how it would be possible to share a set of select expressions across queries. There isn't a way to do this in the SQL standard, so I came up with the following idea. A given query would be marked as a template. This query must be a certain shape, specifically a select from a single table with no additional clauses. You could then refer to that template from subsequent queries to that same table.
Here's an example using the setup from the README
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
-- template: named_author
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors;
-- name: GetAuthor :one
SELECT sqlc.named_author.* FROM authors
WHERE id = $1 LIMIT 1;
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING sqlc.named_author.*;package db
import (
"context"
"database/sql"
)
type NamedAuthor struct {
ID int64
Fullname string
BioDefault string
One int64
}
const createAuthor = `-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
`
type CreateAuthorParams struct {
Name string
Bio sql.NullString
}
func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (NamedAuthor, error) {
row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)
var i NamedAuthor
err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
return i, err
}
const getAuthor = `-- name: GetAuthor :one
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetAuthor(ctx context.Context, id int64) (NamedAuthor, error) {
row := q.db.QueryRowContext(ctx, getAuthor, id)
var i NamedAuthor
err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
return i, err
}