PL/pgSQL function taking argument and searching table recursively

Question

I am brand new in PL/pgSQL. We have a Postgres 11 database.

I need a function that takes 1 argument as an ID of one table and do a recursive search in another. It should return two columns: id and concatenated result as a text (or varchar).

Here is my code (more like pseudocode):

DROP FUNCTION IF EXISTS;
CREATE OR REPLACE FUNCTION my_awesome_func(AUID, UUID) RETURNS (RESULT TEXT) AS;
DECLARE RESULT TEXT;
BEGIN;
SELECT first_table.parent_uid FROM first_table WHERE first_table.auid = AUID; 
WITH RECURSIVE child_to_parents AS (
    SELECT second_table.* FROM second_table
        WHERE aoguid = first_table.parent_uid
    UNION ALL
    SELECT second_table.* FROM second_table.*, child_to_parents
        WHERE second_table.aoguid = child_to_parents.parentguid
            AND second_table.currstatus = 0
    )
END
    SELECT * FROM child_to_parents ORDER BY aolevel;

The fiddle is here:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=dfa2838ab11356cc08987d0931f7b8e5

It is not clear what the function should return. Please add `first_table` and `second_table` create scripts with data and expected result. — Julius Tuskenis, Apr 15 '21 at 07:01
Please create a Fiddle so we can understand your problem: https://dbfiddle.uk/?rdbms=postgres_11 — Frank Heikens, Apr 15 '21 at 07:07
The fiddle is ready. https://dbfiddle.uk/?rdbms=postgres_11&fiddle=dfa2838ab11356cc08987d0931f7b8e5 Thank you. — lomach, Apr 15 '21 at 11:21
Thanks for the sample data (though a table with only the relevant columns would've been sufficient :-P), but it's still not clear how you want to call the function on that and what result you expect, or what the problem with your current code is. — Bergi, Apr 17 '21 at 21:21
The query in the fiddle disagrees with the query in the question. And it's not clear what to concatenate from either. — Erwin Brandstetter, Apr 18 '21 at 01:24

score 0 · Accepted Answer · answered Apr 18 '21 at 02:40

The function you speak of might look something like this:

CREATE OR REPLACE FUNCTION my_awesome_func(_auid uuid, OUT _ancestors text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   WITH RECURSIVE child_to_parents AS (
      SELECT t1.auid, t1.parent_uid, 1 AS lvl
      FROM   first_table t1
      WHERE  t1.auid = _auid 

      UNION ALL
      SELECT c.auid, t2.parentguid, lvl + 1
      FROM   child_to_parents c
      JOIN   second_table t2 ON t2.aoguid = c.parent_uid
                            AND t2.currstatus = 0
      )
   SELECT string_agg(c.parent_uid::text, E'\n'  ORDER BY c.lvl)
   FROM   child_to_parents c
   INTO   _ancestors;
END
$func$;

Filling in with assumptions where your question is unclear.

The plain query needs no PL/pgSQL function wrapper. A plain SQL function, or just the bare query would do (possibly faster).

Related answers with more explanation and links:

PL/pgSQL function taking argument and searching table recursively

1 Answers1