将多个行和列选择到一个记录变量中 [英] SELECT multiple rows and columns into a record variable

问题描述

在plpgsql函数中，如何将多个行和列选择到一个记录变量中?

In a plpgsql function, how can multiple rows and columns be selected into a record variable?

例如，我想将两个列(yearinteger和value)的多个实例SELECT放入记录变量(yearvalues)中.

For example, I would like to SELECT multiple instances of two columns (yearinteger and value) into a record variable (yearvalues).

* EDIT-以下代码只是更长函数的一部分，我需要变量yearvalues包含表中的多行和多列，我可以从中创建更多变量

*EDIT - the following code is just part of a longer function, I need the variable yearvalues to contain multiple rows and columns from a table from which I can create further variables from

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;

推荐答案

plpgsql中没有表变量(至少到v10为止).

There are no table variables in plpgsql (at least up to v10).

您可以使用一个临时表:

You could use a temporary table:

• 从表变量中选择

您可以用CTE(甚至是简单情况下的子查询)代替单个查询的本地范围. 单个查询"可以包含多个命令(在数据修改CTE中).那将是最有效的:

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:

• 从FOR切换在plpgsql中循环到基于集合的SQL命令

或将光标与循环组合(请考虑 FNC-函数下的示例):

Or combine cursors with loops (consider the example under FNC - Function):

• 窗口函数或公用表表达式:计算范围内的前几行

这篇关于将多个行和列选择到一个记录变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！