Re: ***SPAM*** Re: With Recursive / Recursive View question

CREATE OR REPLACE FUNCTION pathname(in_id bigint)

RETURNS character varying AS

$$

DECLARE

  fullpath character varying;

  

BEGIN

  WITH RECURSIVE pathname(id, parent_id, basename) AS (

      SELECT child.id, child.parent_id, child.basename

      FROM dirents child

      WHERE child.id = in_id

    UNION ALL

      SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename)

      FROM dirents parent, pathname child

      WHERE parent.id = child.parent_id

  )

  SELECT basename INTO fullpath FROM pathname where parent_id IS NULL;

  RETURN fullpath;

END;

$$ LANGUAGE plpgsql;

SELECT pathname(id) FROM dirents WHERE basename = 'OSX';