如何在PostgreSQL的函数中编写WITH（CTE） [英] How to write WITH(CTE) within function in PostgreSQL

问题描述

我正在尝试在PostgreSQL函数中使用 WITH，这是Common Table Expression。

I am trying to use "WITH" that is Common Table Expression within the function of PostgreSQL.

以下是示例：

示例：

 Create or replace function withFunction() returns void as
 $Body$
 Begin
  WITH cmn_l1
  AS
  (
    SELECT "PhoneNumber1","PhoneNumber2",
    DENSE_RANK() OVER(Partition by "PhoneNumber1" Order By "PhoneNumber2" )FoundIn
    From tablename;
  )
 SELECT DISTINCT * INTO temptable
 FROM cmn_l1
 WHERE FoundIn > 1;

 end;
 $Body$
 language plpgsql;

问题：如何使用WITH执行并在上表中获取值功能内？

Question: How to execute and get values into the above table using WITH within function?

推荐答案

必须返回表

Create or replace function withFunction()
returns table(phone1 text, phone2 text) as

然后

select * from withFunction()

这篇关于如何在PostgreSQL的函数中编写WITH（CTE）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！