Yesterday, I wrote about some great progress on getting FreshPorts to work with multiple repositories to cater for the branches of the FreeBSD ports tree. At the end of that post, I talked briefly about wanting global settings or variables for my stored procedures. Shortly after posting that, I found a solution in the form of a post by depesz.
A bit about the problem
The problem I encountered was one of context. Sometimes, you want to use trunk (or head) and sometimes you want to use a branch (e.g. RELENG_9_1_0). A function such as the one shown below, has no content. It has ‘head’ hardcoded (see line 8) because the FreeBSD ports tree had no branches when the function was first modified to cater for SVN (FreeBSD previously used CVS).
CREATE OR REPLACE FUNCTION GetPort(text) RETURNS int4 AS $$ DECLARE category_port ALIAS for $1; pathname text; port_element_id int4; port_id int4; BEGIN pathname := '/ports/head/' || category_port; port_element_id := Pathname_ID(pathname); if port_element_id IS NOT NULL THEN select id into port_id from ports where element_id = port_element_id; END IF; return port_id; END; $$ LANGUAGE 'plpgsql';
If you look at yesterday’s post, you’ll see this output:
freshports.org=# select id, name, category, element_id, element_pathname(element_id) from ports_active where name = 'spellathon'; id | name | category | element_id | element_pathname -------+------------+----------+------------+----------------------------------------------- 24964 | spellathon | games | 324899 | /ports/head/games/spellathon 34159 | spellathon | games | 559872 | /ports/branches/RELENG_9_1_0/games/spellathon (2 rows) freshports.org=#
We need some way to tell this function which branch we are using.
This is the amended function, which is working just fine, thank you depesz.
CREATE OR REPLACE FUNCTION GetPort(text) RETURNS int4 AS $$ DECLARE category_port ALIAS for $1; pathname text; port_element_id int4; port_id int4; l_branch text; BEGIN l_branch := freshports_branch_get(); IF l_branch = 'head' THEN pathname := '/ports/' || l_branch || '/' || category_port; ELSE pathname := '/ports/branches/' || l_branch || '/' || category_port; END IF; port_element_id := Pathname_ID(pathname); IF port_element_id IS NOT NULL THEN SELECT id INTO port_id FROM ports WHERE element_id = port_element_id; END IF; RETURN port_id; END; $$ LANGUAGE 'plpgsql';
The new magic code appears on lines 9-15.
- Line 9-10 calls a new function (shown later) which pulls back the branch.
- The IF statement constructs the correct path, based on the differences between trunk and branches in the FreeBSD ports repository.
Show me it working!
I have coded the function to assume head if no branch is specified. That is the default in the current code. Here is what you get when you invoke GetPort without specifying a branch:
freshports.org=# select GetPort('games/spellathon'); getport --------- 24964 (1 row) freshports.org=#
Next, I set the branch, and call the same function again:
freshports.org=# select freshports_branch_set('RELENG_9_1_0'); freshports_branch_set ----------------------- (1 row) freshports.org=# select GetPort('games/spellathon'); getport --------- 34159 (1 row) freshports.org=#
And here we swap back again:
freshports.org=# select freshports_branch_set('head'); freshports_branch_set ----------------------- (1 row) freshports.org=# select GetPort('games/spellathon'); getport --------- 24964 (1 row) freshports.org=#
I was pretty chuffed when I was able to get this working with a minimum of fuss. This bodes well for the rest of this project.
The rest of the code
In addition to the code provided in depesz’s post, I added the following helper functions, specific to FreshPorts.
CREATE OR REPLACE FUNCTION freshports_branch_set( TEXT ) RETURNS void as $$ SELECT session_variables.set_value('branch', $1); $$ language sql; CREATE OR REPLACE FUNCTION freshports_branch_get() RETURNS TEXT as $$ DECLARE reply TEXT; BEGIN reply := session_variables.get_value( 'branch' ); IF reply IS NULL THEN reply := 'head'; END IF; RETURN reply; END; $$ language plpgsql;
The first function lets my code set the branch. You saw me use that in the examples above. The second function is used by the GetPort() function to retrieve the branch it should use. By default, it will return ‘head’.
This session variable stuff is pretty much what I was looking for last night. I’m very pleased that it has been so easy to add to my particular application. Hope it helps you. I’m looking forward to the rest of this upgrade.
If you don’t need the parameter expiry time provided by session_variables package (which I see above that you don’t) , then you don’t need to go through the pain of installing the session_variables schema at all! Postgres provides function set_config(), which can be used to set any two-part GUC variable of one’s choice.
postgres=# select set_config('a.b', 'c', false);
postgres=# show a.b;
postgres=# select current_setting('a.b');
That looks pretty good. And with less setup. I can easily test this too. Thank you. I changed my helper functions:
Seems to work just fine.