PGSQL CTE recursive INSERT RETURNING autoincrement

What I have:

CREATE TABLE public.treeview_menu_node (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    parent_id int8 NULL,
    data jsonb NULL,
    name varchar NULL,
    caption varchar NULL,
    CONSTRAINT treeview_menu_node_pk PRIMARY KEY (id)
);

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(NULL, 'node 1');

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(1, 'node 1.1');

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(1, 'node 1.2');

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(NULL, 'node 2');

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(4, 'node 2.1');

INSERT INTO public.treeview_menu_node
(parent_id, "name")
VALUES(4, 'node 2.2');

Structure:

• node 1
  • sub node 1.1
  • sub node 1.2
• node 2
  • sub node 2.1
  • sub node 2.2

What I need:

Copy node 1 into node 2 recursively

• node 1
  • sub node 1.1
  • sub node 1.2
• node 2
  • sub node 2.1
  • sub node 2.2
  • NODE 1
    • SUB NODE 1.1
    • SUB NODE 1.2

What I try:

WITH RECURSIVE r AS (
    INSERT INTO public.treeview_menu_node (parent_id, name, caption, data)
        SELECT new_parent_id, name, caption, data
        FROM (
            SELECT tmn.id, tmn.parent_id, :parent_id::BIGINT new_parent_id, tmn.name, tmn.caption, tmn.data
            FROM public.treeview_menu_node tmn
            WHERE id IN (:ids)
        ) t
    RETURNING id, parent_id, name, caption, data
    UNION ALL 
    INSERT INTO public.treeview_menu_node (parent_id, name, caption, data)
        SELECT new_parent_id, name, caption, data
        FROM (
            SELECT tmn.id, tmn.parent_id, r.id new_parent_id, tmn.name, tmn.caption, tmn.data
            FROM public.treeview_menu_node tmn
            JOIN r r ON r.id = tmn.parent_id 
        ) t
    RETURNING id, parent_id, name, caption, data
)
SELECT id, parent_id, name, caption, data
FROM r;

Where:

• :parent_id is destination node id
• :ids is a list or one node to copy

What I get:

SQL Error [42601]: syntax error (Near: "UNION") Position: 357

http://sqlfiddle.com/#!17/1e6fa/3

I found a solution.

Multiple inserts in recursive CTE are not allowed. Instead of this, use the function:

NEXTVAL('table_sequensor_of_autoincrement')

The function returns a new id, like an INSERT command. Thanks to this, you can prepare the entire array for insertion using a single INSERT command.

WITH RECURSIVE r AS (
    SELECT tmn.id, NEXTVAL('treeview_menu_id_seq') new_id, tmn.parent_id, :parent_id::BIGINT new_parent_id, tmn.name, tmn.caption, tmn.data
    FROM public.treeview_menu_node tmn
    WHERE id IN (:ids)
    UNION ALL 
    SELECT tmn.id, NEXTVAL('treeview_menu_id_seq') new_id, tmn.parent_id, r.new_id new_parent_id, tmn.name, tmn.caption, tmn.data
    FROM public.treeview_menu_node tmn
    JOIN r r ON r.id = tmn.parent_id 
)
INSERT INTO public.treeview_menu_node (id, parent_id, name, caption, data)
SELECT new_id, new_parent_id, name, caption, data
FROM r;

http://sqlfiddle.com/#!17/1e6fa/10