How to write WITH (CTE) inside a function in PostgreSQL

I am trying to use "WITH" which is a generic table expression in a PostgreSQL function.

Here's an example:

Example :

 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;

      

Question . How do I execute and get the values ​​in the above table using WITH inside a function?

+1


source to share


1 answer


Need to return table

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

      



then

select * from withFunction()

      

+1


source







All Articles