PostgreSQL recursive function returns set of record

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:

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 )
)

Here is my SQL statement (get all childs of a Vertical by specified id):

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;

Could you please help me correct this? Thank you so much!

Solution

In postgresql you have to use statement RETURN if you can push some from function. In your case probably RETURN QUERY ...

BEGIN
  RETURN QUERY WITH RECURSIVE ...
END

and a usage of your function will be much more user friendly if you declare your function as TABLE function

CREATE OR REPLACE FUNCTION xx(..) RETURNS TABLE(a int, b int, ...)

a usage of this function is (PostgreSQL has not statement CALL)

SELECT * FROM xx(..)