hugo-cms/sql/queries/posts.sql

-- name: ListPosts :many
SELECT * FROM posts WHERE site_id = $1 ORDER BY publish_date DESC LIMIT 25;

-- name: GetPostWithID :one
SELECT * FROM posts WHERE id = $1 LIMIT 1;

-- name: ListPublishablePosts :many
SELECT *
FROM posts
WHERE id > $1 AND site_id = $2 AND state = 'published' AND publish_date <= $3
ORDER BY id LIMIT 100;

-- name: InsertPost :one
INSERT INTO posts (
    site_id,
    title,
    body,
    state,
    props,
    publish_date,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
RETURNING id;

-- name: UpdatePost :exec
UPDATE posts SET
    site_id = $2,
    title = $3,
    body = $4,
    state = $5,
    props = $6,
    publish_date = $7,
    updated_at = $8
WHERE id = $1;

-- name: DeletePost :exec
DELETE FROM posts WHERE id = $1;