从光标返回表

Question

我有一个函数，它返回一个基于客户id的计算值表。我需要获取所有客户的值；我制作了一个光标，但无法使其返回集合。

客户表：

id    name
----  ----
CN102 Dude
CN103 Guy
CN104 Mate

功能：

SELECT * FROM get_custom_fields('CN104');
name  field_value
----  -----
POP    9
Z44   blue
POP    19

请注意，可能有多行具有相同的名称。这是我的光标：

CREATE OR REPLACE FUNCTION my_cursor () 
RETURNS SETOF RECORD AS $$
DECLARE
    v_customer_rec RECORD;
    v_pop RECORD;
BEGIN
    FOR v_customer_rec IN SELECT ucn FROM customer LOOP
        SELECT INTO v_pop field_value from get_custom_fields(v_customer_rec.ucn) where custom_field='POP';
        RAISE NOTICE 'Customer % Value %', v_customer_rec.ucn,v_pop;
        --   RETURN QUERY select field_value from get_custom_fields(v_customer_rec.ucn) where custom_field='POP';
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

返回：

db=# select my_cursor();
NOTICE:  Customer CN102 Value (5)
NOTICE:  Customer CN103 Value (12)
NOTICE:  Customer CN104 Value (9)
NOTICE:  Customer CN104 Value (19)
 my_cursor 
-------------
(0 rows)

所以我知道应该工作。但是如果使用RETURN QUERY（如代码中所述），我会得到以下错误：

ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "my_cursor" line 9 at RETURN QUERY

如何使它返回表或集合中的值？

我正试图获得：

ucn   field_value
----- -----------
CN102 5
CN103 12
CN104 9
CN104 19

Answer 1

您的（简化的）函数可能如下所示：

CREATE OR REPLACE FUNCTION my_cursor() 
  RETURNS SETOF RECORD AS
$func$
DECLARE
   _ucn text;
BEGIN
FOR _ucn IN
    SELECT ucn FROM customer
LOOP
    RETURN QUERY
    SELECT *
    FROM   get_custom_fields(_ucn)
    WHERE  name = 'POP';
END LOOP;
RETURN;
END
$func$ LANGUAGE plpgsql;

我在这里假设ucn的数据类型为text。

但实际上，您应该定义RETURN类型，以避免每次调用都必须提供列定义列表。如果您只想要列field_value，并且它是类型text:

CREATE OR REPLACE FUNCTION my_cursor() 
  RETURNS SETOF text AS
$func$
DECLARE
   _ucn text;
BEGIN
FOR _ucn IN
    SELECT ucn FROM customer
LOOP
    RETURN QUERY
    SELECT field_value
    FROM   get_custom_fields(_ucn)
    WHERE  name = 'POP';
END LOOP;
RETURN;
END
$func$ LANGUAGE plpgsql;

或者对每行多列使用RETURNS TABLE()：

CREATE OR REPLACE FUNCTION my_cursor() 
  RETURNS TABLE(ucn text, field_value text) AS
$func$
DECLARE
   _ucn text;
BEGIN
FOR _ucn IN
    SELECT c.ucn FROM customer c
LOOP
    RETURN QUERY
    SELECT g.ucn, g.field_value
    FROM   get_custom_fields(_ucn) g
    WHERE  g.name = 'POP';
END LOOP;
RETURN;
END
$func$ LANGUAGE plpgsql;

请注意，RETURN类型的列在函数体中是可见的。表限定相同名称的列以避免命名冲突。