Postgres Recursive Query, CTE goes in infinite loop inside postgres function using sql?

Question

Trying to make a postgres function, inside of which I made a CTE, to recursively iterate through hierarchical data (Parent child relationship) MY Table structure

In this I need to recursively iterate if text_id != new_text_id and stop (terminating condition) when text_id == new_text_id

Table Schema for reference

create table demoTextTable
(
    text_id serial primary key,
    text_details character varying,
    new_text_id integer
)

insert into demoTextTable(text_details, new_text_id)
values ('Comment 1', 2),
       ('Comment 1 updated 1st Time',3),
       ('Comment 1 updated 2nd Time',4),
       ('Comment 1 updated 3rd Time',5),
       ('Comment 1 updated 4th Time',5);

My Postgres Function

create or replace function get_text_history(textId integer)
RETURNS Table (
    text_id integer,
    text_details varchar,
    new_text_id integer)
AS $$
BEGIN
WITH RECURSIVE textHierarchy AS (

    select tm.text_id, tm.text_details, tm.new_text_id
    from text_master tm where tm.text_id = textId

    UNION ALL

    select tm.text_id, tm.text_details, tm.new_text_id
    FROM textHierarchy AS txtHr, text_master AS tm where tm.text_id != txtHr.new_text_id
    and txtHr.new_text_id is not null
)
select * from textHierarchy;
END;
$$ Language plpgsql;

OK So tried more and got the inside CTE working but if I execute it inside function then it gives error on executing function as

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function get_text_history(integer) line 3 at SQL statement
SQL state: 42601

My Updated Function:-

create or replace function get_text_history(textId integer)
RETURNS Table (
    text_id integer,
    text_details varchar,
    new_text_id integer)
AS $$
BEGIN
WITH RECURSIVE textHierarchy AS (

    select dtbl.text_id, dtbl.text_details, dtbl.new_text_id
    from demoTextTable dtbl where dtbl.text_id = textId

    UNION

    select dtbl.text_id, dtbl.text_details, dtbl.new_text_id
    FROM demoTextTable dtbl where dtbl.text_id != dtbl.new_text_id or 
    dtbl.text_id = dtbl.new_text_id order by text_id asc

)
select * from textHierarchy;
END;
$$ Language plpgsql;

Perhaps you should use UNION rather than UNION ALL to avoid duplicates. Also, your SELECT is lacking an INTO clause. — Laurenz Albe
– Laurenz Albe, Commented Apr 8, 2020 at 9:39
Tried it getting error as record "curr_rec" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. Can you please try it out at your end. — Sanyam Madaan
– Sanyam Madaan, Commented Apr 8, 2020 at 9:45
create or replace function get_text_history(textId integer) RETURNS Table ( text_id integer, text_details varchar, new_text_id integer) AS $$ DECLARE curr_rec record; BEGIN WITH RECURSIVE textHierarchy AS ( select tm.text_id, tm.text_details, tm.new_text_id into curr_rec from text_master tm where tm.text_id = textId UNION select tm.text_id, tm.text_details, tm.new_text_id FROM textHierarchy AS txtHr,text_master AS tm where txtHr.text_id != curr_rec.new_text_id and txtHr.new_text_id is not null ) select * from textHierarchy; END; .. — Sanyam Madaan
– Sanyam Madaan, Commented Apr 8, 2020 at 9:49
No, I cannot try it out, because I don't have the table definitions. Please edit the question and add all relevant information there. Actually, you should use LANGUAGE sql rather than LANGUAGE plpgsql. — Laurenz Albe
– Laurenz Albe, Commented Apr 8, 2020 at 9:54

Laurenz Albe · Accepted Answer · 2020-04-08 11:43:44Z

2

You want to use SQL, not PL/pgSQL, you need UNION rather than UNION ALL, and you need to join with = rather than <>:

CREATE OR REPLACE FUNCTION get_text_history(textId integer)
RETURNS TABLE (
    text_id integer,
    text_details varchar,
    new_text_id integer
) LANGUAGE sql AS
$$WITH RECURSIVE textHierarchy AS (
    SELECT tm.text_id, tm.text_details, tm.new_text_id
    FROM demotexttable tm 
    WHERE tm.text_id = textId
  UNION
    SELECT tm.text_id, tm.text_details, tm.new_text_id
    FROM textHierarchy AS txtHr 
        JOIN demotexttable AS tm ON tm.text_id = txtHr.new_text_id
    WHERE txtHr.new_text_id IS NOT NULL
)
SELECT * FROM textHierarchy$$;

answered Apr 8, 2020 at 11:43

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

8 Comments

Sanyam Madaan Over a year ago

Thanks @Laurenz solution worked like a charm :) But have a doubt why does this function you posted doesn't work with pl/pgSql ?. Why can't table data be returned using pl/pgSql or why does it throws error as 'ERROR: query has no destination for result data'

Laurenz Albe Over a year ago

Because your query has no destination for the result data. Like I wrote in my comment to your question, you need an INTO clause for a SELECT statement in PL/pgSQL, or (what would be the correct thing here) you would need to use RETURN QUERY. But why use all the unnecessary complications of PL/pgSQL syntax if a simple SQL function can do?

Sanyam Madaan Over a year ago

Yes understood but tried it without using INTO and it worked here in next comment

Sanyam Madaan Over a year ago

CREATE OR REPLACE FUNCTION get_text_history(textId integer) RETURNS TABLE ( text_id integer, text_details varchar, new_text_id integer ) AS $$ BEGIN return QUERY WITH RECURSIVE textHierarchy AS ( SELECT tm.text_id, tm.text_details, tm.new_text_id FROM demotexttable tm WHERE tm.text_id = textId UNION SELECT tm.text_id, tm.text_details, tm.new_text_id FROM textHierarchy AS txtHr JOIN demotexttable AS tm ON tm.text_id = txtHr.new_text_id WHERE txtHr.new_text_id IS NOT NULL ) SELECT * FROM textHierarchy; END; $$ Language plpgsql;

Laurenz Albe Over a year ago

Yes, just like I said in my previous comment.

|

Collectives™ on Stack Overflow

Postgres Recursive Query, CTE goes in infinite loop inside postgres function using sql?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related