刚接触存储过程时,我需要在存储过程中执行多个查询并返回结果。我想知道这是否可能。
例如:查询1返回一个userid列表。从用户中选择userid,其中username =?
对于上述查询中的每个userid,我需要执行三个不同的查询,如查询2 select session_details from session where userid =?查询3从userid =?的位置中选择位置
返回值应该是、session_details和location的集合。
这有可能吗,你能提供一些提示吗?
发布于 2017-10-04 00:02:45
仅根据您的示例,以下可能是最简单的方法:
CREATE FUNCTION my_func(user_id INTEGER)
RETURNS TABLE (userid INTEGER, session INTEGER, location TEXT) AS
$$
SELECT u.userid, s.session, l.location
FROM -- etc... your query here
$$
LANGUAGE SQL STABLE;处理评论:这是一个有点不同的问题。一个问题是如何在存储过程中返回包含多个字段的多个记录。一种方法是如上所述。
另一个问题是如何编写从多个表中获取数据的查询。同样,有很多方法可以做到这一点。一种方法是(同样,基于我对示例中需求的解释):
SELECT userid
, ARRAY_AGG(SELECT session_details FROM session s WHERE s.userid = u.userid)
, ARRAY_AGG(SELECT l.location FROM location l WHERE l.userid = u.userid)
FROM user u
WHERE username = user_name这将返回一个记录,其中包含user_id、该用户的session_details数组和该用户的位置数组。
然后,可以将函数更改为:
CREATE FUNCTION my_func(user_name TEXT, OUT userid INTEGER, OUT session_details TEXT[], OUT locations TEXT[])
AS $$
SELECT userid
, ARRAY(SELECT session_details FROM session s WHERE s.userid = u.userid)
, ARRAY(SELECT l.location FROM location l WHERE l.userid = u.userid)
FROM user u
WHERE username = user_name;
$$ LANGUAGE SQL STABLE;发布于 2017-10-04 00:18:36
您可以遍历查询结果,如下所示:
FOR id IN Select userid from user where username = ?
LOOP
...
END LOOP;发布于 2017-10-04 00:31:11
正如@Fahad在他的评论中所说的那样,如果你可以在查询中做到这一点,那就更好了。但如果这是不可能的,你有树的可能性来实现你想要的。
1. SETOF
您可以返回一组值。该集合可以是现有表格、时态表或您定义的类型。
类型示例:
-- In your case the type could be (userid integer, session integer, location text)
CREATE TYPE tester AS (id integer);
-- The pl returns a SETOF the created type.
CREATE OR REPLACE FUNCTION test() RETURNS SETOF tester
AS $$
BEGIN
RETURN QUERY SELECT generate_series(1, 3) as id;
END;
$$ LANGUAGE plpgsql
-- Then, you get the set by selecting the PL as if it were a table.
SELECT * FROM test();表和临时表示例:
-- Create a temporal table o a regular table:
CREATE TEMP TABLE test_table(id integer);
-- or CREATE TABLE test_table(id integer);
-- or use an existing table in your schema(s);
-- The pl returns a SETOF the table you need
CREATE OR REPLACE FUNCTION test() RETURNS SETOF test_table
AS $$
BEGIN
RETURN QUERY SELECT generate_series(1, 3) as id;
END;
$$ LANGUAGE plpgsql
-- Then, you get the set by selecting the PL as if it were a table.
SELECT * FROM test();
-- NOTE: Since you are only returning a SETOF the table,
-- you don't insert any data into the table.
-- So, if you select the 'temp' table you won't see any changes.
SELECT * FROM test_table2.表
-- Next to TABLE you define the columns of the table the PL will return
CREATE OR REPLACE FUNCTION test() RETURNS TABLE (id integer)
AS $$
BEGIN
RETURN QUERY SELECT generate_series(1, 3) as id;
END;
$$ LANGUAGE plpgsql
-- As the other examples, you select the PL to get the data.
SELECT * FROM test();3. refcursor
这个是更复杂的解决方案。您返回的是游标,而不是实际数据。如果你需要返回集合的“动态”值,这就是解决方案。
但由于您需要静态数据,因此不需要此选项。
如果您有一个包含userid、session、location列的表,最好返回一个SETOF表。
如果你只对一个案例使用userid,session,location,那么使用'RETURNS TABLE‘方法会更好。
如果你需要返回一个动态集,你必须使用游标...但这个解决方案确实更高级。
https://stackoverflow.com/questions/46548223
复制相似问题