PostgreSQL – String To Rows

Working Solution – Custom Function

I have to say that using PostgreSQL has been great compared to MySQL. Recently, though, I ran into an issue while working with iReports designer. I needed to be able to accept a string of comma-delimited integers and then JOIN on those ID’s. Therefore, I started looking for a good string splitting function. At first I was unable to find a native one so I altered this plpgsql function made by pilcrow and added some documentation:

CREATE OR REPLACE FUNCTION split_to_rows(
	IN in_text TEXT,
	IN in_delimiter VARCHAR(20)
)
RETURNS SETOF TEXT AS $$
---------------------------------------------------------------------------
-- Function Name: split_to_rows
-- In-coming Params:
--   in_text [TEXT]
--   in_delimiter [VARCHAR(20)]
-- Out going Param:
--   TEXT
-- Description:
--   Splits the specified text with the specified delimiter and returns a
--   set of rows.
-- Created On: 2011-05-24
-- Author: pilcrow (http://stackoverflow.com/questions/1986491#1987113)
-- Modified By: Chris West
---------------------------------------------------------------------------
  DECLARE
    elems text[];      
  BEGIN
    elems := string_to_array(in_text, in_delimiter);
    FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
      RETURN NEXT elems[i];
    END LOOP;
    RETURN;
  END
$$ LANGUAGE 'plpgsql';

The following is an example of using this new function to convert a string of numbers into rows of numbers as integers.

SELECT CAST(split_to_rows('1,2,3,4,5,6', ',') AS INTEGER) AS id;

The Better Solution – Native PostgreSQL Function

After looking into other native solutions, I finally found the regexp_split_to_table function. This is basically called in the same way as pilcrow’s, but with one major difference: you can use regular expressions to split the string into rows. Here is an example of using this new function in the same was as I did the split_to_rows function:

SELECT CAST(regexp_split_to_table('1,2,3,4,5,6', ',') AS INTEGER) AS id;

Escape & Unescape – Deprecated?

Two of the most widely used JavaScript functions that are now deprecated are the escape() and unescape() functions. This means that in order to ensure that your code will continue to work in all future browsers, you should use one of the alternative functions as was indicated on MDN:

  • encodeURI()
  • decodeURI()
  • encodeURIComponent()
  • decodeURIComponent()

What To Do When They Are No More

Since these functions may not always exist, I decided to write the necessary JavaScript code to make sure they can continue to be used:

(function() {
var objGlobal = this;
if(!(objGlobal.escape && objGlobal.unescape)) {
  var escapeHash = {
    _ : function(input) {
      var ret = escapeHash[input];
      if(!ret) {
        if(input.length - 1) {
          ret = String.fromCharCode(input.substring(input.length - 3 ? 2 : 1));
        }
        else {
          var code = input.charCodeAt(0);
          ret = code < 256
            ? "%" + (0 + code.toString(16)).slice(-2).toUpperCase()
            : "%u" + ("000" + code.toString(16)).slice(-4).toUpperCase();
        }
        escapeHash[ret] = input;
        escapeHash[input] = ret;
      }
      return ret;
    }
  };
  objGlobal.escape = objGlobal.escape || function(str) {
    return str.replace(/[^\w @\*\-\+\.\/]/g, function(aChar) {
      return escapeHash._(aChar);
    });
  };
  objGlobal.unescape = objGlobal.unescape || function(str) {
    return str.replace(/%(u[\da-f]{4}|[\da-f]{2})/gi, function(seq) {
      return escapeHash._(seq);
    });
  };
}
})();

Reference

I created these functions based on information found at DevGuru.com.

Forcing A Constructor In JavaScript

Did you know that you can force a function to only act as a constructor? The following is an example of a Person pseudo-class which can be defined with or without the new keyword:

// Defines a person object.
function Person(firstName, lastName, age) { 
  // If called without parameters.
  if(!arguments.length) {
    // If this is a recursive call, allow the properties to now be defined.
    if(arguments.callee.caller === arguments.callee) {
      return this;
    }
    // If this is a non-recursive call, throw an error since the constructor
    // can't be called without parameters.
    else {
      throw new Error("No parameters were specified for the Person object.");
    }
  }
  
  // Allows make sure this function acts as a constructor.
  var me = !(this instanceof arguments.callee) ? new Person : this;
    
  // Define the properties.
  me.firstName = firstName;
  me.lastName = lastName;
  me.age = age;
  
  // Return a reference to the new Person instance.
  return me;
}

// Define the prototypal functions.
Person.prototype = {
  toString : function() {
    return this.firstName + " " + this.lastName + " is " + this.age
      + " years old.";
  }
};

One of the things that you may have noticed is the fact that I am throwing an error if the constructor is called without any arguments. If you don’t want that to happen, you can simply remove that else statement. Everything is probably pretty self explanatory.

The following are two examples which prove the above code actually works:

// Create an instance of a person without the "new" keyword.
var p = Person("Chris", "West", 23);
alert(p.firstName);  // display "Chris"
alert(p instanceof Person);  // display true
alert(p);  // use toString() function

// Create an instance of a person with the "new" keyword.
var p = new Person("Tamara", "Thomas", 21);
alert(p.age);  // display 21
alert(p instanceof Person);  // display true
alert(p);  // use toString() function.