如何通过 INT id 从 PostgreSQL 函数返回值数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35374093/
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
How to return array of values from PostgreSQL function by INT id
提问by Mr. Rosario Maddox
I am trying to create a simple PostgreSQL function, where by using INT parameter I like to get array back. The example below will not work, but shall give idea of what I try to get back from a function. Thanks.
我正在尝试创建一个简单的 PostgreSQL 函数，通过使用 INT 参数，我想取回数组。下面的示例将不起作用，但可以让我了解我试图从函数中得到什么。谢谢。
CREATE OR REPLACE FUNCTION contact_countries_array(INT)
RETURNS ANYARRAY AS '
SELECT ARRAY[contacts_primarycountry, contacts_othercountry] FROM contacts WHERE contacts_id = '
LANGUAGE SQL;
The data type of contacts_primarycountry and contacts_othercountry is integer. contacts_id is unique and integer.
contacts_primarycountry 和contacts_othercountry 的数据类型是整数。contacts_id 是唯一的整数。
回答by Hambone
Per the docs:
根据文档：
It is permitted to have polymorphic arguments with a fixed return type, but the converse is not.
允许具有固定返回类型的多态参数，但反之则不然。
As such, I think your attempt to return anyarraywon't work.
因此，我认为您返回的尝试是anyarray行不通的。
Your fields look like text, so I think if you altered it to something like this, it would work:
您的字段看起来像文本，所以我认为如果您将其更改为这样的内容，它将起作用：
CREATE OR REPLACE FUNCTION contact_countries_array(INT)
RETURNS text[] AS $$
select array[contacts_primarycountry::text, contacts_othercountry::text]
FROM contacts WHERE contacts_id =
$$
LANGUAGE SQL;
This should compile, and it mightwork, but I'm honestly not sure:
这应该可以编译，并且可能会起作用，但老实说我不确定：
CREATE OR REPLACE FUNCTION contact_countries_array(anyelement)
RETURNS anyarray AS $$
select array[contacts_primarycountry::text, contacts_othercountry::text]
FROM contacts WHERE contacts_id =
$$
LANGUAGE SQL;
I think the datatypes would have to match perfectly for this to work, unless you did casting.
我认为数据类型必须完全匹配才能正常工作，除非您进行了转换。
回答by Thushara Buddhika
Declaring Array, Looping, Adding items to Array, Returning Array with Postgres Function,
声明数组、循环、向数组添加项、使用 Postgres 函数返回数组、
You can declare INTEGERarray instead of TEXTand avoid casting (counter::TEXT)as well as return type TEXT[]. (Added those for reference.)
您可以声明INTEGER数组而不是TEXT并避免强制转换(counter::TEXT)以及返回类型TEXT[]。（添加了那些以供参考。）
CREATE OR REPLACE FUNCTION "GetNumbers"(maxNo INTEGER) RETURNS TEXT[] AS $nums$
DECLARE
counter INTEGER := 0;
nums TEXT[] := ARRAY[]::TEXT[];
BEGIN
LOOP
EXIT WHEN counter = maxNo;
counter = counter + 1;
nums = array_append(nums, counter::TEXT);
END LOOP;
RETURN nums;
END ;
$nums$ LANGUAGE plpgsql;
SELECT "GetNumbers"(5); -- {1,2,3,4,5}
