sqlc-multi-db/example/db/query.postgres.sql at 565258921ce85466f758734e7ac425c87da85dbf · kalbasit/sqlc-multi-db · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- name: GetBook :one
-- SELECT "id", "title", "author", "description", "created_at", "updated_at" FROM books WHERE id = $1
SELECT "id", "title", "author", "description", "created_at", "updated_at"
FROM books
WHERE id = $1;

-- name: ListBooks :many
-- SELECT "id", "title", "author", "description", "created_at", "updated_at" FROM books ORDER BY title
SELECT "id", "title", "author", "description", "created_at", "updated_at"
FROM books
ORDER BY title;

-- name: GetBooksByAuthor :many
-- SELECT "id", "title", "author", "description", "created_at", "updated_at" FROM books WHERE author = $1
SELECT "id", "title", "author", "description", "created_at", "updated_at"
FROM books
WHERE author = $1;

-- name: CreateBook :one
-- INSERT INTO books ("title", "author", "description") VALUES ($1, $2, $3) RETURNING "id", "title", "author", "description", "created_at", "updated_at"
INSERT INTO books ("title", "author", "description")
VALUES ($1, $2, $3)
RETURNING "id", "title", "author", "description", "created_at", "updated_at";

-- name: UpdateBook :one
-- UPDATE books SET "title" = $1, "author" = $2, "description" = $3, "updated_at" = NOW() WHERE id = $4 RETURNING "id", "title", "author", "description", "created_at", "updated_at"
UPDATE books
SET "title" = $1,
    "author" = $2,
    "description" = $3,
    "updated_at" = NOW()
WHERE id = $4
RETURNING "id", "title", "author", "description", "created_at", "updated_at";

-- name: DeleteBook :exec
-- DELETE FROM books WHERE id = $1
DELETE FROM books
WHERE id = $1;

-- name: CreateTag :one
-- INSERT INTO tags ("name") VALUES ($1) RETURNING "id", "name", "created_at", "updated_at"
INSERT INTO tags ("name")
VALUES ($1)
RETURNING "id", "name", "created_at", "updated_at";

-- name: GetTag :one
-- SELECT "id", "name", "created_at", "updated_at" FROM tags WHERE id = $1
SELECT "id", "name", "created_at", "updated_at"
FROM tags
WHERE id = $1;

-- name: AddBookTag :exec
-- INSERT INTO book_tags ("book_id", "tag_id") VALUES ($1, $2)
INSERT INTO book_tags ("book_id", "tag_id")
VALUES ($1, $2);

-- name: AddBookTags :exec
-- INSERT INTO book_tags ("book_id", "tag_id") VALUES (unnest($1::bigint[]), unnest($2::bigint[]))
-- @bulk-for AddBookTag
INSERT INTO book_tags ("book_id", "tag_id")
VALUES (unnest(sqlc.arg(book_ids)::bigint[]), unnest(sqlc.arg(tag_ids)::bigint[]));

-- name: GetBookTags :many
-- SELECT t."id", t."name", t."created_at", t."updated_at" FROM tags t INNER JOIN book_tags bt ON t.id = bt.tag_id WHERE bt.book_id = $1
SELECT t."id", t."name", t."created_at", t."updated_at"
FROM tags t
INNER JOIN book_tags bt ON t.id = bt.tag_id
WHERE bt.book_id = $1;