postgresql 循环遍历 RECORD 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13065774/
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
Loop through columns of RECORD
提问by RKI
I need to loop through type RECORDitems by key/index, like I can do this using array structures in other programming languages.
我需要RECORD通过键/索引遍历类型项，就像我可以使用其他编程语言中的数组结构来做到这一点一样。
For example:
例如：
DECLARE
data1 record;
data2 text;
...
BEGIN
...
FOR data1 IN
SELECT
*
FROM
sometable
LOOP
FOR data2 IN
SELECT
unnest( data1 ) -- THIS IS DOESN'T WORK!
LOOP
RETURN NEXT data1[data2]; -- SMTH LIKE THIS
END LOOP;
END LOOP;
回答by Erwin Brandstetter
As @Pavel explained, it is not simply possible to traverse a record, like you could traverse an array. But there are several ways around it - depending on your exact requirements. Ultimately, since you want to return all values in the same column, you need to cast them to the same type - textis the obvious common ground, because there is a text representation for every type.
正如@Pavel 所解释的那样，不能像遍历数组那样简单地遍历记录。但是有几种方法可以解决它 - 取决于您的确切要求。最终，由于您想返回同一列中的所有值，您需要将它们强制转换为相同的类型——这text是明显的共同点，因为每种类型都有一个文本表示。
Quick and dirty
又快又脏
Say, you have a table with an integer, a textand a datecolumn.
假设您有一个包含integer、atext和date列的表。
CREATE TEMP TABLE tbl(a int, b text, c date);
INSERT INTO tbl VALUES
(1, '1text', '2012-10-01')
,(2, '2text', '2012-10-02')
,(3, ',3,ex,', '2012-10-03') -- text with commas
,(4, '",4,"ex,"', '2012-10-04') -- text with commas and double quotes
Then the solution can be a simple as:
那么解决方案可以很简单：
SELECT unnest(string_to_array(trim(t::text, '()'), ','))
FROM tbl t;
Works for the first two rows, but fails for the special cases of row 3 and 4.
You can easily solve the problem with commas in the text representation:
适用于前两行，但不适用于第 3 行和第 4 行的特殊情况。
您可以在文本表示中使用逗号轻松解决问题：
SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])
FROM tbl t
WHERE a < 4;
This would work fine - except for line 4 which has double quotes in the text representation. Those are escaped by doubling them up. But the array constructor would need them escaped by \. Not sure why this incompatibility is there ...
这会正常工作 - 除了在文本表示中有双引号的第 4 行。那些是通过加倍逃脱的。但是数组构造函数需要将它们转义为\. 不知道为什么会出现这种不兼容...
SELECT ('{' || trim(t::text, '()') || '}') FROM tbl t WHERE a = 4
Yields:
产量：
{4,""",4,""ex,""",2012-10-04}
But you would need:
但你需要：
SELECT '{4,"\",4,\"ex,\"",2012-10-04}'::text[]; -- works
Proper solution
正确的解决方案
If you knew the column names beforehand, a clean solution would be simple:
如果您事先知道列名，一个干净的解决方案将很简单：
SELECT unnest(ARRAY[a::text,b::text,c::text])
FROM tbl
Since you operate on records of well know type you can just query the system catalog:
由于您对已知类型的记录进行操作，因此您只需查询系统目录：
SELECT string_agg(a.attname || '::text', ',' ORDER BY a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'tbl'::regclass
AND a.attnum > 0
AND a.attisdropped = FALSE
Put this in a function with dynamic SQL:
把它放在一个带有动态 SQL 的函数中：
CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
RETURNS SETOF text LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE '
SELECT unnest(ARRAY[' || (
SELECT string_agg(a.attname || '::text', ',' ORDER BY a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = _tbl::regclass
AND a.attnum > 0
AND a.attisdropped = false
) || '])
FROM ' || _tbl::regclass;
END
$func$;
Call:
称呼：
SELECT unnest_table('tbl') AS val
Returns:
返回：
val
-----
1
1text
2012-10-01
2
2text
2012-10-02
3
,3,ex,
2012-10-03
4
",4,"ex,"
2012-10-04
This works without installing additional modules. Another option is to install the hstoreextension and use it like @Craig demonstrates.
这无需安装其他模块即可工作。另一种选择是安装hstore扩展并像@Craig 演示一样使用它。
回答by Craig Ringer
PL/pgSQL isn't really designed for what you want to do. It doesn't consider a record to be iterable, it's a tuple of possibly different and incompatible data types.
PL/pgSQL 并不是真正为您想要做的事情而设计的。它不认为记录是可迭代的，它是一个可能不同且不兼容的数据类型的元组。
PL/pgSQL has EXECUTEfor dynamic SQL, but EXECUTEqueries cannot refer to PL/pgSQL variables like NEWor other records directly.
PL/pgSQL 具有EXECUTE动态 SQL，但EXECUTE查询不能直接引用 PL/pgSQL 变量NEW或其他记录。
What you cando is convert the record to a hstorekey/value structure, then iterate over the hstore. Use each(hstore(the_record)), which produces a rowset of key,valuetuples. All values are cast to their textrepresentations.
您可以做的是将记录转换为hstore键/值结构，然后遍历hstore. 使用each(hstore(the_record))，它产生一个key,value元组行集。所有值都转换为它们的text表示。
This toy function demonstrates iteration over a record by creating an anonymous ROW(..)- which will have column names f1, f2, f3- then converting that to hstore, iterating over its column/value pairs, and returning each pair.
这个玩具函数通过创建一个匿名记录来演示迭代ROW(..)- 它将具有列名f1, f2, f3- 然后将hstore其转换为，迭代其列/值对，并返回每一对。
CREATE EXTENSION hstore;
CREATE OR REPLACE FUNCTION hs_demo()
RETURNS TABLE ("key" text, "value" text)
LANGUAGE plpgsql AS
$$
DECLARE
data1 record;
hs_row record;
BEGIN
data1 = ROW(1, 2, 'test');
FOR hs_row IN SELECT kv."key", kv."value" FROM each(hstore(data1)) kv
LOOP
"key" = hs_row."key";
"value" = hs_row."value";
RETURN NEXT;
END LOOP;
END;
$$;
In reality you would never write it this way, since the whole loop can be replaced with a simple RETURN QUERYstatement and it does the same thing each(hstore)does anyway - so this is onlyto show how each(hstore(record))works, and the above function should never actually be used.
实际上，您永远不会以这种方式编写它，因为可以用一个简单的RETURN QUERY语句替换整个循环，each(hstore)并且无论如何它都会做同样的事情- 所以这只是为了展示如何each(hstore(record))工作，并且永远不应该实际使用上述函数。
回答by Pavel Stehule
This feature is not supported in plpgsql - Record IS NOT hash array like other scripting languages - it is similar to C or ADA, where this functionality is impossible. You can use other PL language like PLPerl or PLPython or some tricks - you can iterate with HSTORE datatype (extension) or via dynamic SQL
plpgsql 不支持此功能 - 记录不是像其他脚本语言那样的哈希数组 - 它类似于 C 或 ADA，但此功能是不可能的。您可以使用其他 PL 语言，如 PLPerl 或 PLPython 或一些技巧 - 您可以使用 HSTORE 数据类型（扩展）或通过动态 SQL 进行迭代
see How to set value of composite variable field using dynamic SQL
But request for this functionality usually means, so you do some wrong. When you use PL/pgSQL you have think different than you use Javascript or Python
但是请求这个功能通常意味着，所以你做错了。当您使用 PL/pgSQL 时，您的想法与使用 Javascript 或 Python 不同
回答by Clodoaldo Neto
FOR data2 IN
SELECT d
from unnest( data1 ) s(d)
LOOP
RETURN NEXT data2;
END LOOP;
回答by Loek Bergman
If you order your results prior to looping, will you accomplish what you want.
如果您在循环之前对结果进行排序，您会完成您想要的。
for rc in select * from t1 order by t1.key asc loop
return next rc;
end loop;
will do exactly what you need. It is also the fastest way to perform that kind of task.
会做你需要的。这也是执行此类任务的最快方法。
