我试图在PostgreSQL函数中使用常用的表表达式“WITH”。
下面是一个例子:
例子:
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 WITH函数执行并获取上表中的值?
最佳答案
必须返回table
Create or replace function withFunction()
returns table(phone1 text, phone2 text) as
然后
select * from withFunction()