首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Postgres存储过程/函数

问Postgres存储过程/函数
EN

Stack Overflow用户
提问于 2017-10-03 23:29:04
回答 3查看 642关注 0票数 0

刚接触存储过程时,我需要在存储过程中执行多个查询并返回结果。我想知道这是否可能。

例如:查询1返回一个userid列表。从用户中选择userid,其中username =?

对于上述查询中的每个userid,我需要执行三个不同的查询,如查询2 select session_details from session where userid =?查询3从userid =?的位置中选择位置

返回值应该是、session_details和location的集合。

这有可能吗,你能提供一些提示吗?

EN

回答 3

Stack Overflow用户

发布于 2017-10-04 00:02:45

仅根据您的示例,以下可能是最简单的方法:

代码语言:javascript
复制
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;

处理评论:这是一个有点不同的问题。一个问题是如何在存储过程中返回包含多个字段的多个记录。一种方法是如上所述。

另一个问题是如何编写从多个表中获取数据的查询。同样,有很多方法可以做到这一点。一种方法是(同样,基于我对示例中需求的解释):

代码语言:javascript
复制
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数组和该用户的位置数组。

然后,可以将函数更改为:

代码语言:javascript
复制
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;
票数 0
EN

Stack Overflow用户

发布于 2017-10-04 00:18:36

您可以遍历查询结果,如下所示:

代码语言:javascript
复制
FOR id IN   Select userid from user where username = ?
LOOP

...
 END LOOP;
票数 0
EN

Stack Overflow用户

发布于 2017-10-04 00:31:11

正如@Fahad在他的评论中所说的那样,如果你可以在查询中做到这一点,那就更好了。但如果这是不可能的,你有树的可能性来实现你想要的。

  1. SETOF
  2. TABLE
  3. refcursor

1. SETOF

您可以返回一组值。该集合可以是现有表格、时态表或您定义的类型。

类型示例:

代码语言:javascript
复制
-- 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();

表和临时表示例:

代码语言:javascript
复制
-- 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_table

2.表

代码语言:javascript
复制
-- 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‘方法会更好。

如果你需要返回一个动态集,你必须使用游标...但这个解决方案确实更高级。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/46548223

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档