How to write WITH(CTE) within function in PostgreSQL

Question

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

Here is the following 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 to execute and get values into the above table using WITH within function?

Clodoaldo Neto · Accepted Answer · 2014-06-13 12:45:04Z

7

It it necessary to return table

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

then

select * from withFunction()

answered Jun 13, 2014 at 12:45

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarfaraz Makandar Over a year ago

Okay. So there is no need to give EXECUTE or PERFORM statement before WITH?

Clodoaldo Neto Over a year ago

@Meem execute is used for dynamic sql and perform when you don't want to return the result of the select

Sarfaraz Makandar Over a year ago

@ Clodoaldo Neto, Absolutely right. Thank you so much.

Collectives™ on Stack Overflow

How to write WITH(CTE) within function in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related