postgresql PL/pgSQL：我们可以将查询结果存储到变量中吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9187874/
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
PL/pgSQL: Can we store a query result into a variable
提问by Mayank
I'm trying to create a function in plpgsql like:
我正在尝试在 plpgsql 中创建一个函数，例如：
CREATE OR REPLACE FUNCTION select_left_photo_ids(in_photo_id bigint[], in_album_id bigint, in_limit int) RETURNS SETOF bigint[] AS
$$
DECLARE photo_count int;
DECLARE photo_ids bigint[];
BEGIN
SELECT photo_id INTO STRICT photo_ids FROM tbl_album_photos WHERE album_id = in_album_id AND photo_id < in_photo_id ORDER BY photo_id DESC LIMIT in_limit;
GET DIAGNOSTICS photo_count = ROW_COUNT;
IF photo_count < in_limit THEN
SELECT photo_id INTO STRICT photo_ids FROM (SELECT photo_id FROM tbl_album_photos WHERE album_id = in_album_id ORDER BY photo_id LIMIT in_limit) AS dummy ORDER BY photo_id DESC;
END IF;
RETURN photo_ids;
END;
$$
LANGUAGE plpgsql;
The idea is to fetch the photo ids that are greater than input photo ids. If the no. of photos in the result comes out to be less than limit, I will try to fetch bottom n records.
这个想法是获取大于输入照片 ID 的照片 ID。如果没有。结果中的照片数小于限制，我将尝试获取后 n 条记录。
The above function does not work. Could somebody please give a some pointers/hints or links on how to store result of a select query in a variable.
上述功能不起作用。有人可以提供一些关于如何将选择查询的结果存储在变量中的指针/提示或链接。
Note: photo_id is a bigint[]data type - I mean its intentionally bigint[].
注意： photo_id 是一种bigint[]数据类型 - 我的意思是它故意的bigint[]。
回答by mu is too short
You need to add an array_aggto
您需要将添加array_agg到
- Get a single value from your query so that SELECT...INTO will work.
- And get a
bigint[]result to put into yourbigint[]target.
- 从您的查询中获取单个值，以便 SELECT...INTO 可以工作。
- 并得到一个
bigint[]结果来放入你的bigint[]目标中。
Then you'll need to add a derived table to get your LIMIT to work.
然后你需要添加一个派生表来让你的 LIMIT 工作。
select array_agg(photo_id) into strict photo_ids
from (
select photo_id
from tbl_album_photos
where album_id = in_album_id
and photo_id < in_photo_id
order by photo_id desc
limit in_limit
) dt;
Then you can ask the array how big it is instead of looking at ROW_COUNT:
然后你可以问数组有多大，而不是看 ROW_COUNT：
photo_count := array_length(photo_ids, 1);
And then the next SELECT...INTO would be similar to the new array_aggversion above.
然后下一个 SELECT...INTO 将类似于array_agg上面的新版本。
