How to recursively return table in postgres function using plpgsql? Without using CTE/With Recursive

Question

I tried implementing it here as follows

create or replace function getTextEditRecord(textId integer)
RETURNS Table (
    text_id integer,
    text_details character varying,
    new_text_id integer) AS $$
DECLARE
    curr_rec record;
    temp_rec record;
BEGIN
    curr_rec :=
        (select tm.text_id, tm.text_details, tm.new_text_id from text_master tm
        where tm.text_id = textId);
    IF FOUND THEN
        IF curr_rec.text_id != curr_rec.new_text_id THEN
            temp_rec := getTextEditRecord(curr_rec.new_text_id);
            --RETURN TABLE HERE
        ELSE
            -- No Recursive call directly return table
            --RETURN TABLE HERE
        END IF;
    END IF;
    --RETURN TABLE HERE
END;
$$ Language plpgsql;

Now I tried searching on google but could find that how to typecast record type to table type. As in convert temp_rec to table type and return.

Why don't you use a recursive CTE and get rid of the slow and inefficient loop (and cursor) — user330315
– user330315, Commented Apr 7, 2020 at 7:19
First want to learn this way and then move to its advancements. — Sanyam Madaan
– Sanyam Madaan, Commented Apr 7, 2020 at 7:23
You should focus on learning the efficient, scalable and correct solutions first. Then you can move on to the in-efficient and non-scalable ones, if you really know what you are doing. — user330315
– user330315, Commented Apr 7, 2020 at 7:26
Has been asked first to do this way :) Please help if you know. Been stuck at it from last night. — Sanyam Madaan
– Sanyam Madaan, Commented Apr 7, 2020 at 7:27

user330315 · Accepted Answer · 2020-04-07 07:46:51Z

2

Doing this via a recursive function is the most inefficient way of doing it.

But anyhow, you can do something like this:

create or replace function get_text_edit_record(p_text_id integer)
RETURNS Table (
    text_id integer,
    text_details varchar,
    new_text_id integer) 
AS $$
DECLARE
    curr_rec record;
BEGIN
  select tm.text_id, tm.text_details, tm.new_text_id 
    into curr_rec
  from text_master tm
  where tm.text_id = p_text_id;

  IF FOUND THEN
    return query 
     select curr_rec.text_id, curr_rec.text_details, curr_rec.new_text_id;

    --- IS DISTINCT FROM properly deals with NULL values
    IF curr_rec.text_id IS DISTINCT FROM curr_rec.new_text_id THEN
        return query 
         select * 
         from get_text_edit_record(curr_rec.new_text_id);
     END IF;
  END IF;
END;
$$ Language plpgsql;

answered Apr 7, 2020 at 7:46

user330315

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

4 Comments

Sanyam Madaan Over a year ago

Is it possible to copy/clone curr_rec to temp_rec ?

user330315 Over a year ago

@SanyamMadaan: you don't need a second record, but you can simply assign it: temp_rec := curr_rec;

Sanyam Madaan Over a year ago

Tried it but it gives error as ERROR: record "temp_rec" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: SQL statement "SELECT temp_rec.text_id"

Sanyam Madaan Over a year ago

Now tried creating using recursive cte. Facing a problem can you look please here. stackoverflow.com/questions/61096799/…

Collectives™ on Stack Overflow

How to recursively return table in postgres function using plpgsql? Without using CTE/With Recursive

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related