Functions in sql that returns table (Postgres SQL)

Question

I have these following query that gives me back a table:

WITH RECURSIVE EXPL (parent_node, node, filename) AS
     (
      SELECT ROOT.parent_node, ROOT.node, ROOT.filename
      FROM   filesystem   ROOT
      WHERE  ROOT.parent_node = 1

      UNION ALL

      SELECT CHILD.parent_node, CHILD.node, CHILD.filename
      FROM   EXPL PARENT, filesystem CHILD
      WHERE  PARENT.node = CHILD.parent_node
     )

     SELECT   DISTINCT parent_node, node, filename
     FROM     EXPL
     ORDER BY parent_node, node;
END

But I want a function that replaces WHERE ROOT.parent_node = 1 with WHERE ROOT.parent_node = x. X is an integer that is the functions parameter.

I thought about, but is not correct at all:

CREATE FUNCTION childs(x INT) RETURNS TABLE 
RETURN
WITH RECURSIVE EXPL (parent_node, node, filename) AS
     (
      SELECT ROOT.parent_node, ROOT.node, ROOT.filename
      FROM   filesystem   ROOT
      WHERE  ROOT.parent_node = x

      UNION ALL

      SELECT CHILD.parent_node, CHILD.node, CHILD.filename
      FROM   EXPL PARENT, filesystem CHILD
      WHERE  PARENT.node = CHILD.parent_node
     )

     SELECT   DISTINCT parent_node, node, filename
     FROM     EXPL
     ORDER BY parent_node, node;
END;

Tag your question with the database you are using. Not all databases support user defined table functions. — Gordon Linoff
– Gordon Linoff, Commented May 27, 2018 at 12:48

Haleemur Ali · Accepted Answer · 2018-05-27 14:03:20Z

2

Your function definition was missing a BEGIN & a QUERY after RETURN

CREATE OR REPLACE FUNCTION childs(x int) 
RETURNS TABLE (parent_node integer, node integer, filename varchar(255)) AS
$function$
BEGIN 
RETURN QUERY
WITH RECURSIVE expl (parent_node, node, filename) AS
     (
      SELECT root.parent_node, root.node, root.filename
      FROM   filesystem   root
      WHERE  root.parent_node = x

      UNION ALL

      SELECT child.parent_node, child.node, child.filename
      FROM   expl parent, filesystem child
      WHERE  parent.node = child.parent_node
     )

     SELECT   DISTINCT expl.parent_node, expl.node, expl.filename
     FROM     expl
     ORDER BY parent_node, node;
END 
$function$ LANGUAGE plpgsql;

Use the function like this:

SELECT * FROM childs(1)

edited May 27, 2018 at 14:03

answered May 27, 2018 at 13:40

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michel Milezzi Over a year ago

Upvoted. Suggestion: you can simplify a bit by changing from language plpgsql to sql, and then removing begin/end, return, etc.

Marc Over a year ago

After calling SELECT * FROM childs(1) I get the error: structure of query does not match function result type. Returned type character varying(255) does not match expected type text in column 3.

Haleemur Ali Over a year ago

@Marc, can you update the question with some sample data so I can test it.

Marc Over a year ago

@Haleemur Ali just did

Haleemur Ali Over a year ago

@Marc, the error happened because I guessed the types you were using. see updated answer.

Collectives™ on Stack Overflow

Functions in sql that returns table (Postgres SQL)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related