Column doesn't exist when using WITH statement PostgreSQL

Question

I want to create a function to be used to get the node traversal path.

CREATE TYPE IDType AS (id uuid);

drop function F_ItemPath;
CREATE OR REPLACE FUNCTION F_ItemPath (item record)
RETURNS TABLE (item_id uuid, depth numeric)
AS $$
BEGIN
return QUERY
    WITH recursive item_path AS (
        SELECT ic.parent_item_id, depth=1
        from item_combination ic, item i 
        WHERE ic.child_item_id=i.id
        UNION all
        SELECT ic.parent_item_id, depth=ip.depth + 1
        FROM item_path ip, item_combination ic WHERE ip.parent_item_id=ic.child_item_id
        )
        SELECT item_id=ip.parent_item_id, depth=ip.depth FROM item_path ip;
END; $$
LANGUAGE plpgsql;

select * from F_ItemPath(('55D6F516-7D8F-4DF3-A4E5-1E3F505837A1', 'FFE2A4D3-267C-465F-B4B4-C7BB2582F1BC'))

there has two problems:

1. I tried using user-defined type to set parameter type CREATE TYPE IDType AS (id uuid);, but I don't know how to call the function with table argument
2. there has an error that says:

SQL Error [42703]: ERROR: column ip.depth does not exist
  Where: PL/pgSQL function f_itempath(record) line 3 at RETURN QUERY

what I expected is I can use the function normally and the argument can be supplied from other tables.

this is the full query that you can try: http://sqlfiddle.com/#!15/9caba/1
I made the query in DBEAVER app, it will have some different error message.
I suggest you can experiment with it outside sqlfiddle.

Answer 1

The expression depth=1 tests if the column depth equals the value 1 and returns a boolean value. But you never give that boolean expression a proper name.

Additionally you can't add numbers to boolean values, so the expression depth=ip.depth + 1 tries to add 1 to a value of true or false - which fails obviously. If it did work, it would then compare that value with the value in the column depth again.

Did you intend to alias the value 1 with the name depth? Then you need to use 1 as depth and ip.depth + 1 as depth in the recursive part.

In the final select you have the same error - using boolean expressions instead of a column alias

It's also highly recommended to use explicit JOIN operators which were introduced in the SQL standard over 30 years ago.

Using PL/pgSQL to wrap a SQL query is also a bit of an overkill. A SQL function is enough.

Using an untyped record as a parameter seems highly dubious. It won't allow you to access columns using e.g. item.id. But given your example call, it seems you simply want to pass multiple IDs for the anchor (no-recursive) part of the query. That's better done using an array or a varadic parameter which allows listing multiple parameters with commas.

So you probably want something like this:

drop function f_itempath;
CREATE OR REPLACE FUNCTION f_itempath(variadic p_root_id uuid[])
  RETURNS TABLE (item_id uuid, depth integer)
as
$$  
  WITH recursive item_path AS (
    SELECT ic.parent_item_id, 1 as depth
    FROM item_combination ic
    WHERE ic.child_item_id = any(p_root_id) --<< no join needed to access the parameter
    UNION all
    SELECT ic.parent_item_id, ip.depth + 1
    FROM item_path ip
       JOIN item_combination ic ON ip.parent_item_id = ic.child_item_id
  )
  SELECT ip.parent_item_id as item_id, ip.depth 
  FROM item_path ip;
$$
language sql
stable;

Then you can call it like this (note: no parentheses around the parameters)

select * 
from f_itempath('55d6f516-7d8f-4df3-a4e5-1e3f505837a1', 'ffe2a4d3-267c-465f-b4b4-c7bb2582f1bc');

select * 
from f_itempath('55d6f516-7d8f-4df3-a4e5-1e3f505837a1', 'ffe2a4d3-267c-465f-b4b4-c7bb2582f1bc', 'df366232-f200-4254-bad5-94e11ea35379');

select * 
from f_itempath('55d6f516-7d8f-4df3-a4e5-1e3f505837a1');