postgresql SELECT 多行和多列到一个记录变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24949266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT multiple rows and columns into a record variable
提问by interpost
In a plpgsql function, how can multiple rows and columns be selected into a record variable?
在plpgsql函数中，如何将多行多列选择到一个记录变量中？
For example, I would like to SELECTmultiple instances of two columns (yearintegerand value) into a record variable (yearvalues).
例如，我想将SELECT两列 (yearinteger和value) 的多个实例放入一个记录变量 ( yearvalues) 中。
*EDIT - the following code is just part of a longer function, I need the variable yearvaluesto contain multiple rows and columns from a table from which I can create further variables from
*编辑 - 以下代码只是一个较长函数的一部分，我需要该变量yearvalues包含一个表中的多行和多列，我可以从中创建更多的变量
CREATE OR REPLACE FUNCTION fn_function ()
RETURNS TABLE () AS $$
DECLARE
year c.year%TYPE;
value c.value%TYPE;
yearvalues record;
BEGIN
FOR yearvalues IN
SELECT c.year, c.value FROM c
LOOP
END LOOP;
-- creation of additional variables from the yearvalues variable
END;
$$ LANGUAGE plpgsql;
回答by Erwin Brandstetter
There are no table variables in plpgsql (at least up to v10).
plpgsql 中没有表变量（至少到 v10）。
You could use a temporary table:
您可以使用临时表：
You can substitute with CTEs (or even subqueries in simple cases) for the local scope of a single query. A "single query" can encompass multiple commands (in data-modifying CTEs). That would be most efficient:
您可以用 CTE（甚至简单情况下的子查询）替换单个查询的本地范围。“单个查询”可以包含多个命令（在数据修改 CTE 中）。那将是最有效的：
Or combine cursors with loops (consider the example under FNC - Function):
或者将游标与循环组合（考虑FNC - Function下的示例）：
