1

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.

Sanyam Madaan
  • 139
  • 4
  • 14
  • Why don't you use a recursive CTE and get rid of the slow and inefficient loop (and cursor) –  Apr 07 '20 at 07:19
  • First want to learn this way and then move to its advancements. – Sanyam Madaan Apr 07 '20 at 07: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. –  Apr 07 '20 at 07:26
  • Has been asked first to do this way :) Please help if you know. Been stuck at it from last night. – Sanyam Madaan Apr 07 '20 at 07:27

1 Answers1

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;
  • Is it possible to copy/clone curr_rec to temp_rec ? – Sanyam Madaan Apr 07 '20 at 11:58
  • @SanyamMadaan: you don't need a second record, but you can simply assign it: `temp_rec := curr_rec;` –  Apr 07 '20 at 12:00
  • 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 Apr 07 '20 at 12:06
  • Now tried creating using recursive cte. Facing a problem can you look please here. https://stackoverflow.com/questions/61096799/postgres-recursive-query-cte-goes-in-infinite-loop-inside-postgres-function-usi – Sanyam Madaan Apr 08 '20 at 11:11