sql - plpgsql 如何在函数或过程中执行查询

Question

我开始在 postgres 数据库中学习 sql 中的存储过程和函数。我需要一个例子来帮助我实现我想要完成的目标。

我需要运行一个过程并让它返回结果。例如这样的事情:

run_query(name):
 begin
 return select * from employees where first_name = $name
 end
end

我想要类似上面的东西在我运行时返回结果集。这可能吗？提前谢谢你的帮助!这是我试图创建的函数:

CREATE OR REPLACE FUNCTION test() RETURNS TABLE(id INT, subdomain varchar, launched_on_xxx timestamp, UVs bigint, PVs bigint) AS
  'SELECT dblink_connect(''other_DB'');
  SELECT    c.id as id, c.subdomain, c.launched_on_xxx, COALESCE(SUM(tbd.new_unique_visitors), 0) AS UVs, COALESCE(SUM(tbd.page_views), 0) AS PVs
    FROM    dblink(''SELECT id, subdomain, launched_on_xxx FROM communities'')
            AS c(id int, subdomain character varying, launched_on_xxx timestamp)
        LEFT OUTER JOIN days_of_center tbd
            ON  c.id = tbd.community_id
    WHERE   c.launched_on_xxx < now()
    GROUP BY    c.id, c.subdomain, c.launched_on_xxx;
    SELECT dblink_disconnect();'
      LANGUAGE SQL;

Answer 1

您的函数可能如下所示:

CREATE OR REPLACE FUNCTION test()
  RETURNS TABLE(id int, subdomain varchar, launched_on_xxx timestamp
               ,uvs bigint, pvs bigint) AS
$func$
SELECT dblink_connect('other_DB');

SELECT c.id
      ,c.subdomain
      ,c.launched_on_xxx
      ,COALESCE(SUM(tbd.new_unique_visitors), 0) AS uvs
      ,COALESCE(SUM(tbd.page_views), 0) AS pvs
FROM   dblink('
         SELECT id, subdomain, launched_on_xxx
         FROM   communities
         WHERE  launched_on_xxx < now()')
       AS c(id int, subdomain varchar, launched_on_xxx timestamp)
LEFT   JOIN days_of_center tbd ON tbd.community_id = c.id
GROUP  BY c.id, c.subdomain, c.launched_on_xxx;

SELECT dblink_disconnect();
$func$ LANGUAGE SQL;

• 将 WHERE 子句拉入 dblink 函数。从一开始就不获取行比从外部数据库中获取行然后丢弃它们要有效得多。

• 使用美元引用以避免与引用混淆。这已成为具有更大功能定义的标准程序。

要以“表格格式”输出它，调用一个返回多列的函数，如下所示:

SELECT * FROM test();