A single function for creating a new port

In my two previous posts I talked about creating a new port and copying a port from head to a branch.

The goal of this post is the creation of a new function: CreatePortOnBranch($category_name, $port_name, $CommitBranch)

The failed start

I started out with this stored procedure:

CREATE OR REPLACE FUNCTION CreatePort(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_element_id_of_new_port int4;
  l_port_id                int4;

  BEGIN

WITH RECURSIVE all_descendents AS (
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/sysutils/bacula-client-static/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
   SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/'), directory_file_flag), 
                      replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
      FROM all_descendents
     WHERE status = 'A';

  SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
    INTO l_port_id;
 
  RETURN l_port_id;

  END;
$$  LANGUAGE 'plpgsql';

Running it gave this message:

# select CreatePort('sysutils', 'bacula-server', '2016Q2');
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function createport(text,text,text) line 12 at SQL statement

I tried PERFORM, but failed to find a suitable syntax. I found out why. I asked on IRC:

I am trying to use PERFORM in order to discard the result set in a plpgsql function.. I think I want PERFORM, but I’m failing. https://gist.github.com/dlangille/3f464ac2537028f0602551f1bf4db2ba

The replies I got (NOTE: the entire channel log is not shown here):

[Jul 02 12:33]  <RhodiumToad>	perform and with have ... problems
[Jul 02 12:33]  <RhodiumToad>	PERFORM works by replacing the word PERFORM with SELECT and then parsing the query
[Jul 02 12:34]  <RhodiumToad>	so PERFORM WITH ...  never works
[Jul 02 12:36]  <dvl>	RhodiumToad: Sounds like I should create a function with just that WITH RECURSION in it, and call that instead.
[Jul 02 12:36]  <RhodiumToad>	that would probably be simpler
[Jul 02 12:36]  <Myon>	enricostn: that's not a locales problem, that's a version mismatch problem, 9.3 vs 9.5
[Jul 02 12:36]  <johto>	you could also do  PERFORM 1 FROM (<query>) ss;  because there are no wCTEs
[Jul 02 12:37]  <RhodiumToad>	yeah, but that's really ugly
[Jul 02 12:37]  <enricostn>	Myon: oh... would you please so kind to post there and shade some light? Would be really appreciated.
[Jul 02 12:38]  <dvl>	I also think dividing this up into distinct functions will have code benefits later.

The two new functions and a new datatype

Based on the above discussion, I created these functions:

--
-- this function copies a port from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH
-- It returns the id of the new port.
--
CREATE OR REPLACE FUNCTION CopyPortFromHeadToBranch(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_r             element_id_pathname%rowtype;
  l_port_id       int4;

  BEGIN

  FOR l_r IN
  WITH RECURSIVE all_descendents AS ( 
  SELECT id, name, parent_id, directory_file_flag, status
    FROM element
    WHERE id = (select pathname_id('/ports/head/' || a_category_name || '/' || a_port_name || '/'))
  UNION
  SELECT E.id, E.name, E.parent_id, E.directory_file_flag, E.status
    FROM element E
    JOIN all_descendents AD
      ON (E.parent_id = AD.id)
)
    SELECT element_add(replace(element_pathname(id), '/ports/head/', '/ports/branches/' || a_branch_name || '/'), directory_file_flag),
                       replace(element_pathname(id), '/ports/head/', '/ports/branches/2016Q2/')
      FROM all_descendents
     WHERE status = 'A'
  LOOP
  END LOOP;

  IF FOUND THEN
    SELECT pathname_id('/ports/branches/' || a_branch_name || '/' || a_category_name || '/' || a_port_name || '/')
      INTO l_port_id;
  END IF;

  return l_port_id;
  END;
$$  LANGUAGE 'plpgsql';

With this helper function created, the original function becomes:

--
-- this function create a port on a branch using head as a starting point.
-- i.e. from /ports/head/CATEGORY/PORT to /ports/branches/BRANCH/CATEGORY/PORT
-- is created.
-- It returns the id of the new port.
--
CREATE OR REPLACE FUNCTION CreatePort(text, text, text) returns int4 AS $$
DECLARE
  a_category_name ALIAS for $1;
  a_port_name     ALIAS for $2;
  a_branch_name   ALIAS for $3;

  l_element_id_of_new_port int4;
  l_port_id                int4;

  BEGIN

  SELECT CopyPortFromHeadToBranch(a_category_name, a_port_name, a_branch_name)
    INTO l_element_id_of_new_port;

  IF FOUND THEN
    SELECT createport(l_element_id_of_new_port, getcategory(a_category_name))
      INTO l_port_id;
  END IF;
 
  RETURN l_port_id;

  END;
$$  LANGUAGE 'plpgsql';

Here is the first test of that function:

select CreatePort('sysutils', 'bacula-docs', '2016Q2');

 createport 
------------
      41762
(1 row)

Let’s see the full pathname of that port:

# select element_pathname(element_id) from ports where id = 41762;
              element_pathname               
---------------------------------------------
 /ports/branches/2016Q2/sysutils/bacula-docs
(1 row)

Success.