如何实现结合my_numbers表与number_generator序列的指定逻辑函数

Got it, let's work through this problem properly. First off, I noticed your table creation statement has a tiny syntax error—there's an extra semicolon inside the parentheses. Let's fix that first, then build the function that does exactly what you need.

First, let's get the sequence and table set up with valid syntax:

CREATE SEQUENCE number_generator AS INT;

CREATE TABLE my_numbers (
    num INT PRIMARY KEY
);

Below is a PostgreSQL function that implements your requested logic. It checks if my_numbers has rows, deletes one and returns the value if so; otherwise, it pulls the next value from the sequence.

CREATE OR REPLACE FUNCTION get_next_number()
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    deleted_num INT;
BEGIN
    -- Attempt to delete a single row and capture its value
    DELETE FROM my_numbers
    LIMIT 1
    RETURNING num INTO deleted_num;

    -- If we deleted a row, return that number
    IF deleted_num IS NOT NULL THEN
        RETURN deleted_num;
    ELSE
        -- If the table was empty, grab the next sequence value
        RETURN nextval('number_generator');
    END IF;
END;
$$;

How This Function Works #

• We declare a variable deleted_num to store the value of any row we delete.
• The DELETE ... RETURNING clause tries to remove one row from my_numbers and saves the deleted num value to our variable.
• If deleted_num isn't null (meaning a row existed and was deleted), we return that value immediately.
• If the table was empty, deleted_num stays null, so we call nextval() to fetch the next increment from the number_generator sequence and return that instead.

Quick Usage Example #

To use the function, just call it like any other SQL function:

SELECT get_next_number();

内容的提问来源于stack exchange，提问作者quantum_well