Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write WITH(CTE) within function in PostgreSQL

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?

like image 422
Sarfaraz Makandar Avatar asked Aug 03 '26 17:08

Sarfaraz Makandar


1 Answers

It it necessary to return table

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

then

select * from withFunction()
like image 51
Clodoaldo Neto Avatar answered Aug 06 '26 08:08

Clodoaldo Neto



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!