Handle tree structure in SQL

See more:

SQL

PostgreSQL

I have to handle a table PRODUCTS which is created to accommodate tree structure of products. It is done to handle situations when one product can contain several others (e.g. one package product holds several other positions). So, I'm making a function that takes OrderDetails, and it must iterate through all PRODUCTS and list out the child products for each product listed. I am facing an issue that I have to iterate through tree of unknown depth. Please, give me an idea how to do it.

UPD. Tried to implement such structure in PostgreSQL, here is the code:

CREATE TABLE public.tree_products (
  product_id INTEGER DEFAULT nextval('ree_products_product_id_seq'::regclass) NOT NULL,
  name VARCHAR,
  parent_id INTEGER,
  CONSTRAINT ree_products_pkey PRIMARY KEY(product_id)
) 

CREATE OR REPLACE FUNCTION foo()RETURNS text AS
$body$
DECLARE _row RECORD;
		_result text := '';
        _child_row RECORD;
        _count integer := -1;
        _marker integer := 1;
BEGIN
	FOR _row IN SELECT * FROM tree_products
    LOOP
    	_result := _result || _marker || ' ' || _row.name;
    	_count := (SELECT count(product_id) FROM tree_products WHERE parent_id = _row.product_id);
        IF _count > 0 THEN
        	FOR _child_row IN SELECT * FROM tree_products WHERE parent_id = _row.product_id
            LOOP
            	_result := _result || ' ' || _child_row.name;
            END LOOP;
        END IF;
        _marker := _marker =1;   	
   	END LOOP;
END;
$body$
LANGUAGE plpgsql

That function goes throuh products table, but the depth of childs search is limited to 1. I want to have unlimited depth of listing.