PostgreSQL plpgsql函数报错：subquery must return only one column 求助

The error subquery must return only one column occurs because you're trying to assign an entire row from a subquery to a %rowtype variable using the assignment operator (:=). PostgreSQL interprets this syntax as expecting a single scalar value, not a full multi-column row, hence the syntax violation.

The Core Fix #

Instead of using curProd := (SELECT ...), use PL/pgSQL's native SELECT ... INTO syntax. This is the standard, intended way to populate row-type variables with full query results.

Replace this problematic line:

curProd := ( SELECT "KeysForSale".* FROM "KeysForSale" WHERE row_STab.product_id = "KeysForSale".product_id AND (("KeysForSale".begin_date < payment_date AND "KeysForSale".end_date > payment_date) OR ("KeysForSale".discounted_price IS NULL)) ORDER BY "KeysForSale".discounted_price ASC NULLS LAST LIMIT 1 );

With this corrected version:

SELECT "KeysForSale".* INTO curProd
FROM "KeysForSale"
WHERE row_STab.product_id = "KeysForSale".product_id
  AND (("KeysForSale".begin_date < payment_date AND "KeysForSale".end_date > payment_date) OR ("KeysForSale".discounted_price IS NULL))
ORDER BY "KeysForSale".discounted_price ASC NULLS LAST
LIMIT 1;

Improved Row Missing Handling #

When using SELECT ... INTO, PostgreSQL throws a NO_DATA_FOUND exception by default if no rows are returned. To align with your original logic of checking for missing products, replace the IF curProd IS NULL check with a FOUND variable check (PostgreSQL automatically sets this after queries):

-- Replace the original NULL check with this
IF NOT FOUND THEN
    RAISE EXCEPTION 'Product is not available for purchase.';
END IF;

Full Corrected Function #

Here's the complete fixed function with all adjustments:

CREATE FUNCTION "paymentRun"(buyer_id integer, payment_date DATE, payMethod paymentMethod, paid_amount double precision, payDetails text) 
RETURNS VOID AS $$ 
DECLARE 
    row_STab "SearchTable"%rowtype; 
    curProd "KeysForSale"%rowtype; 
    totalPrice double precision; 
    returnedPID integer; 
BEGIN 
    --For each entry in the search table 
    FOR row_STab IN ( SELECT * FROM "SearchTable" ) LOOP 
        --We retrieve the associated product info, together with an available key 
        SELECT "KeysForSale".* INTO curProd
        FROM "KeysForSale"
        WHERE row_STab.product_id = "KeysForSale".product_id
          AND (("KeysForSale".begin_date < payment_date AND "KeysForSale".end_date > payment_date) OR ("KeysForSale".discounted_price IS NULL))
        ORDER BY "KeysForSale".discounted_price ASC NULLS LAST
        LIMIT 1;

        --Either there is no such product, or no keys for it 
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Product is not available for purchase.';
        END IF;

        --Product's seller is the buyer - we can't let that pass 
        IF curProd.user_id = buyer_id THEN 
            RAISE EXCEPTION 'A Seller cannot purchase their own product.'; 
        END IF; 

        --Fill in the rest of the data to prepare the purchase 
        UPDATE "SearchTable" 
        SET "SearchTable".price = ( 
            CASE 
                WHEN curProd.discounted_price IS NOT NULL THEN curProd.discounted_price 
                ELSE curProd.price 
            END 
        ), "SearchTable".sk_id = curProd.sk_id 
        WHERE "SearchTable".product_id = curProd.product_id; 
    END LOOP; 

    --Get total cost (using INTO for cleaner variable assignment)
    SELECT SUM("SearchTable".price) INTO totalPrice FROM "SearchTable"; 

    --The given price does not match the actual cost? 
    IF totalPrice <> paid_amount THEN 
        RAISE EXCEPTION 'Payment does not match cost!'; 
    END IF; 

    --Create a purchase while keeping it's ID for register 
    INSERT INTO "Purchases" (purchase_id, final_price, user_id, paid_date, payment_method, details) 
    VALUES (DEFAULT, totalPrice, buyer_id, payment_date, payMethod, payDetails) 
    RETURNING purchase_id INTO returnedPID; 

    --For each product we wish to purchase 
    FOR row_STab IN ( SELECT * FROM "SearchTable" ) LOOP 
        INSERT INTO "PurchasedKeys"(sk_id, purchase_id, price) 
        VALUES (row_STab.sk_id, returnedPID, row_STab.price); 

        UPDATE "SerialKeys" 
        SET "SerialKeys".user_id = buyer_id 
        WHERE row_STab.sk_id = "SerialKeys".sk_id; 
    END LOOP; 
END $$ 
LANGUAGE plpgsql;

Quick Additional Tips #

• Removed unnecessary quotes around plpgsql in the LANGUAGE clause (it's a keyword, not an identifier)
• Used SELECT ... INTO for the totalPrice assignment to keep the code consistent
• The FOUND variable is a reliable way to check if a query returned rows, avoiding edge cases with NULL row values

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