1

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!

0

1 Answer 1

5

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(..)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.