PostgreSQL – Sum Of An Array

Something I had to do today was figure out how to find the SUM of an array in PostgreSQL. I went through many different ways, but this seemed to be the best:

SELECT SUM(t) FROM UNNEST(ARRAY[1,2,3,4,5,6,7,8,9]) t;

If you know of a shorter or better way to do this for a variable size array please let me know in the comments section below. If it is better, I will post it and of course give you all the credit. 8)

PostgreSQL – Granting Access To All Views

Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views:

SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' TO john_doe;'
FROM pg_views
WHERE schemaname = 'public';

After running a query like this one, you will be given all of the GRANT statements needed to give SELECT permissions to all of the views in the public schema.

PostgreSQL – Escape Regular Expressions

Someone had an issue today where they were using a string as if it were a regular expression. Not totally understanding what the desired end result would be, I had suggested they use something equivalent to Java’s Pattern.quote() function. It turned out that they really just wanted to determine if one string was equivalent to another, but the issue still made me wonder: is there an equivalent in PostgreSQL which gives you the ability to get a literal regular expression pattern string for any string? I unfortunately didn’t find anything in the documentation pages for PostgreSQL so I wrote the following function definition:

CREATE OR REPLACE FUNCTION public.regexp_quote(IN TEXT)
  RETURNS TEXT
  LANGUAGE plpgsql
  STABLE
AS $$
/*******************************************************************************
 * Function Name: regexp_quote
 * In-coming Param:
 *   The string to decoded and convert into a set of text arrays.
 * Returns:
 *   This function produces a TEXT that can be used as a regular expression
 *   pattern that would match the input as if it were a literal pattern.
 * Description:
 *   Takes in a TEXT in and escapes all of the necessary characters so that
 *   the output can be used as a regular expression to match the input as if
 *   it were a literal pattern.
 * Created On: 2012-07-10
 * Updated On: 2012-07-10
 * Author: Chris West
 ******************************************************************************/
BEGIN
  RETURN REGEXP_REPLACE($1, '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');
END;
$$

This function can be in the following way:

SELECT regexp_quote('[]{}'); -- produces "\\[\\]\\{\\}"

You really don’t have to define a function if you don’t want to. You could just use the following form to do the equivalent:

-- produce "\\[\\]\\{\\}"
SELECT REGEXP_REPLACE('[]{}', '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');