CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsql
Sanyam Madaan
Sanyam Madaan

Reputation: 139

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

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.

Upvotes: 1

Views: 562

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions