Postgresql supabase 中的交易

有两种方法可以实现这一点:

1. 将逻辑封装在postgres函数中，然后将其称为RPC.类似于:

CREATE OR REPLACE FUNCTION create_post_and_comment(
    post_title TEXT,
    post_body TEXT,
    comment_body TEXT
) RETURNS VOID AS $$
BEGIN
    -- Insert a new post into the "posts" table
    INSERT INTO posts (title, body)
    VALUES (post_title, post_body)
    RETURNING id INTO id;

    -- Insert a new comment into the "comments" table
    INSERT INTO comments (post_id, body)
    VALUES (id, comment_body);
END;
$$ LANGUAGE plpgsql;

2. 在POST表上创建触发器以创建 comments 条目:

-- Create a function that inserts a comment
CREATE OR REPLACE FUNCTION insert_comment()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO comments (post_id, body)
    VALUES (NEW.id, 'Default comment text'); -- You can change the default comment text as needed
    RETURN NULL; -- Returning NULL because we are only inserting into "comments" and not modifying the "posts" table
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after inserting into "posts"
CREATE TRIGGER after_insert_post
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION insert_comment();

请注意，如果您将每个帖子中的所有 comments 存储在JSON/JSONB字段中，则触发器将是一种更好的方法.