title: PostgreSQL recursive function returns set of record
body: <p>I am writing a PostgreSQL recursive function which returns a set of records, but I do not know the correct syntax of it. The recursive table here is Vertical which contains below columns:  </p>
  
  <pre><code>CREATE TABLE vertical
  (
    id serial NOT NULL,
    name character varying(100) NOT NULL,
    insert_timestamp timestamp without time zone NOT NULL DEFAULT now(),
    deleted smallint NOT NULL DEFAULT 0,
    parent_id integer, // refer to another Vertical
    locations character varying(200),
    level smallint NOT NULL DEFAULT 1,
    CONSTRAINT vertical_pkey PRIMARY KEY (id ),
    CONSTRAINT vertical_parent_fkey FOREIGN KEY (parent_id)
        REFERENCES vertical (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT name_ukey UNIQUE (name )
  )
  </code></pre>
  
  <p>Here is my SQL statement (get all childs of a Vertical by specified id):  </p>
  
  <pre><code>CREATE FUNCTION getAllVerticalChilds(verticalId integer) RETURNS SETOF RECORD AS 
  $BODY$
  BEGIN
     WITH RECURSIVE childs(id, name, parent_id, level) AS (
     SELECT child.id, child.name, child.parent_id, child.level
     FROM vertical child
     WHERE child.parent_id = verticalId
     UNION ALL
     SELECT parent.id, parent.name, parent.parent_id, parent.level
     FROM childs child
     INNER JOIN vertical parent ON parent.parent_id = child.id where parent.deleted=0
     )
     SELECT id, name, parent_id, level
     FROM childs;
  END
  $BODY$
  LANGUAGE plpgsql VOLATILE;
  </code></pre>
  
  <p>Could you please help me correct this? Thank you so much!</p>