sql - void 函数抛出 'query has no destination for result data' 错误

Question

我有一个返回类型为 void 的函数(包括整个函数):

CREATE OR REPLACE FUNCTION insert_avg_prices(p_county character varying, p_municipality character varying, p_district character varying, p_neighbourhood character varying, p_street character varying, p_number character varying, p_avg_rent decimal, p_avg_sale decimal)
 RETURNS VOID AS
$BODY$
DECLARE
    _precint_id integer;
    _city_id integer;
    _parish_id integer;
    _county_id integer;

    _address_id integer;

BEGIN
    SELECT place_precint.precint_id INTO _precint_id FROM place_precint WHERE place_precint.name = p_neighbourhood;
    SELECT place_city.city_id INTO _city_id FROM place_city WHERE place_city.name = p_district;
    SELECT place_parish.parish_id INTO _parish_id FROM place_parish WHERE place_parish.name = p_municipality;
    SELECT place_county.county_id INTO _county_id FROM place_county WHERE place_county.name = p_county;

    SELECT full_addresses.f_address_id FROM full_addresses 
    WHERE  full_addresses.county_id = _county_id AND
           full_addresses.parish_id = _parish_id AND
           full_addresses.city_id = _city_id AND
           full_addresses.precint_id = _precint_id AND
           full_addresses.street_name = p_street AND
           full_addresses.number = p_number;

    IF _address_id IS NULL THEN
        INSERT INTO full_addresses(f_address_id,
                     county_id,
                     parish_id,
                     city_id,
                     precint_id,
                     street_name,
                     number)
        VALUES (nextval('full_addresses_f_address_id_seq'),
            _county_id,
            _parish_id,
            _city_id,
            _precint_id,
            p_street,
            p_number);

        SELECT currval('full_addresses_f_address_id_seq') INTO _address_id;
    END IF;

    INSERT INTO building_prices(building_id, 
                    address_id, 
                    avg_sau_sale_price, 
                    avg_sau_rent_price) 
    VALUES (nextval('building_prices_building_id_seq'),
        _address_id,
        p_avg_sale,
        p_avg_rent);
    RETURN;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

如果我用这样的 select 语句调用它:

SELECT insert_avg_prices(...);

我得到一个错误:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function insert_avg_prices(character varying,character varying,character varying,character varying,character varying,character varying,numeric,numeric) line 16 at SQL statement
********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

CREATE FUNCTION 语句没有错误。如何让它发挥作用？

Answer 1

在您的函数中，您有一个 SELECT 语句，但该语句无处可放置其数据。

• 如果the statement returns exactly 1 row , 使用 INTO 子句将结果存储在局部变量中进​​行处理
• 如果语句返回多行，使用选项之一 loop over the results或 use a CURSOR .

显然错误在这里:

SELECT full_addresses.f_address_id FROM full_addresses WHERE ...

应该是

SELECT full_addresses.f_address_id INTO _address_id FROM full_addresses WHERE ...

请注意，PL/pgSQL 是一种解释型语言，这意味着函数体不会像 Pascal 或 C++ 等编译型编程语言那样评估其语法完整性。因此，您可以编写一个仅在调用时显示任何错误的函数。