Category Archives: SQL

POW – Explain That SQL #1

Someone asked me to quickly write them some code in any language that would accomplish a certain task. The following, which is in PostgreSQL, is the second solution that I came up with after doing the first one in my favorite language, JavaScript:

SELECT ARRAY_TO_STRING(ARRAY(
    SELECT CHR(num
        + CASE
            WHEN num < 26 THEN 65
            WHEN num BETWEEN 26 AND 51 THEN 71
            ELSE -4
          END
      )
    FROM (
      SELECT FLOOR(RANDOM() * 62)::INT AS num,
        generate_series(1, 12)
    ) AS t
  ), '')

The question is, what does this SELECT statement actually do? If you were asked to write that as a JavaScript, substituting the 12 for a variable passed into the function, what would your function look like?

As usual, the answer to this Problem of the Week will be made available a week from today, on Wednesday, August 22, 2012.

The answer to this Problem of the Week can now be found here.

POW – Fill In The Blanks In SQL

One of the things I always find interesting is the fact that DBAs almost always use some type of auto-increment method in order to generate unique IDs for records. Sometimes, though, you may have a table in which you don’t want to use the default method of generating the next ID in the sequence. Sometimes the goal is to re-use old IDs that are no longer in use. The goal this week will be to create a SELECT statement in any flavor of SQL you want, that will fetch the smallest non-negative positive integer that has not yet been used. The column will be called id while the table will be called stars. What is your most efficient way that you can think of the accomplish this task using just a SELECT statement (not a self-written function)?

The answer to this Problem of the Week is here.

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');

PostgreSQL – Granting Access To All Schemas

Another DBA task that I seem to have to carry out is making sure that a specified user or group has all (CREATE & USAGE) permissions to all of the schemas (except pg_catalog for which they only get USAGE) in the database.  For this reason, I end up using the following SELECT statement to generate all of the needed GRANT statements:

SELECT DISTINCT 'GRANT '
    || CASE schemaname WHEN 'pg_catalog' THEN 'USAGE' ELSE 'ALL' END
    || ' ON SCHEMA ' || quote_ident(schemaname) || ' TO john_doe;'
FROM pg_tables;

The above query can be modified in whatever way that you like so that it can create a GRANT statement such as is outlined on the corresponding PostgreSQL 8.1+ documentation page.  Of course, there may be a better way (such as using a table that has only one row for each schema name). If you have another way to grant privileges to a schema, please let me know.

PostgreSQL – Selecting the Newest Records

One of the things that I often had a difficult time with was finding a way to select the newest records from a table that had one or more columns to group by and then one column to order by. Let’s use the following table as an example:

Version Name Group
1 Viejo A
2 Joven A
1 Oldest B
2 Old B
3 New B
1 Jovencito C
4 Newest B

Using the above table, I want to be able to pull all of the rows that have the newest version based on the_group. I also only want to have to query the table once. With this in mind, after a lot of research, I found two solutions. The first solution, which is the shorter one, involves using the DISTINCT ON clause:

SELECT DISTINCT ON (the_group) *
FROM my_table
ORDER BY the_group DESC, version DESC;

[hide-this-part morelink=”Solution Without Table”]

SELECT DISTINCT ON (the_group) *
FROM (
  SELECT 1 AS version, 'Viejo' AS name, 'A' AS the_group
  UNION SELECT 2 AS version, 'Joven' AS name, 'A' AS the_group
  UNION SELECT 1 AS version, 'Oldest' AS name, 'B' AS the_group
  UNION SELECT 2 AS version, 'Old' AS name, 'B' AS the_group
  UNION SELECT 3 AS version, 'New' AS name, 'B' AS the_group
  UNION SELECT 1 AS version, 'Jovencito' AS name, 'C' AS the_group
  UNION SELECT 4 AS version, 'Newest' AS name, 'B' AS the_group
) t
ORDER BY the_group DESC, version DESC;

[/hide-this-part]

The second solution that I was able to concoct is a bit less elegant and uses a WINDOW FRAME:

SELECT version, name, the_group
FROM (
  SELECT *,
    first_value(version) OVER (PARTITION BY the_group ORDER BY version DESC) AS latest_version
  FROM my_table
) t
WHERE version = latest_version;

[hide-this-part morelink=”Solution Without Table”]

SELECT version, name, the_group
FROM (
  SELECT *,
    first_value(version) OVER (PARTITION BY the_group ORDER BY version DESC) AS latest_version
  FROM (
    SELECT 1 AS version, 'Viejo' AS name, 'A' AS the_group
    UNION SELECT 2 AS version, 'Joven' AS name, 'A' AS the_group
    UNION SELECT 1 AS version, 'Oldest' AS name, 'B' AS the_group
    UNION SELECT 2 AS version, 'Old' AS name, 'B' AS the_group
    UNION SELECT 3 AS version, 'New' AS name, 'B' AS the_group
    UNION SELECT 1 AS version, 'Jovencito' AS name, 'C' AS the_group
    UNION SELECT 4 AS version, 'Newest' AS name, 'B' AS the_group
  ) t
) t
WHERE version = latest_version;

[/hide-this-part]

The downside of the above solution is the fact that you are using at least one sub-query. The other downside is the fact that you have to list each field that you want to bring back if you don’t want to bring back the extra field indicating the latest_version. One last downside about the second solution is the fact that it does seem to take a little bit longer than the first solution. For these reason I feel that the solution using the DISTINCT ON clause is the best one.

PostgreSQL – Convert A String Into A Table

Recently at work we had the need to convert a string parameter (passed into an iReport) into a table. For many people, the first thing that may come to mind is either the STRING_TO_ARRAY(...) function combined with the UNNEST(...) function. The biggest issue with using just those two functions is the fact that you don’t have any way of allowing whatever delimiters that will be used for cells and rows to remain. For this reason, I created the following PL/pgSQL function which uses the commas as cell delimiters and semicolons as row delimiters:

CREATE OR REPLACE FUNCTION public.uri_decode_2d_array(IN input TEXT)
  RETURNS SETOF TEXT[]
  LANGUAGE plpgsql
  STABLE
AS $function$
/*******************************************************************************
 * Function Name: uri_decode_2d_array
 * In-coming Params:
 *   input [TEXT] - The string to decoded and convert into a set of text arrays.
 * Returns:
 *      TEXT
 * Description:
 *   Takes in a string and converts it to a set of arrays.
 * Created On: 2012-06-14
 * Updated On: 2012-06-14
 * Author: Chris West
 ******************************************************************************/
BEGIN
  RETURN QUERY
    SELECT array_agg(t)
    FROM (
      SELECT
        REPLACE(REPLACE(REPLACE(UNNEST(a), '%2C', ','), '%3B', ';'), '%25', '%') AS t,
        ROW_NUMBER() OVER (ORDER BY 1) AS r
      FROM (
        SELECT STRING_TO_ARRAY(UNNEST(STRING_TO_ARRAY(input, ';')), ',') a
      ) t
    ) t
    GROUP BY r
    ORDER BY r;
END;
$function$

Now the question is, how do you convert your 2D array into a string that will be interpreted by the above SQL function? The following PHP function can do just that:

function uriEncode2DArray($inputArray) {
  $ret = "";
  foreach($inputArray as $kOuter => $vOuter) {
    if($kOuter) {
      $ret .= ";";
    }
    foreach($vOuter as $kInner => $vInner) {
      $ret .= ($kInner ? "," : "") . cryptThis($vInner);
    }
  }
  return $ret;
}

The following is an example of converting a 2D array into a string using the above function:

$arr = array(
  array("Jen Harring", "001238192", "January 1, 1987"),
  array("Tim Alekper", "902340340", "June 30, 1987", "Mister 99%; AKA Vampire")
);
echo uriEncode2DArray($arr);
# The above outputs the following:
#   Jen Harring,001238192,January 1%2C 1987;Tim Alekper,902340340,June 30%2C 1987,Mister 99%25%3B AKA Vampire

The following is an example of how you would use the generated string to generate a table:

SELECT a[1]::VARCHAR AS name,
  REGEXP_REPLACE(a[2]::VARCHAR, '(...)(..)(....)', '\\1-\\2-\\3') AS ssn,
  a[3]::DATE AS date_of_birth,
  age(a[3]::TIMESTAMP) AS age,
  a[4]::TEXT AS comments
FROM uri_decode_2d_array('Jen Harring,001238192,January 1%2C 1987;Tim Alekper,902340340,June 30%2C 1987,Mister 99%25%3B AKA Vampire') AS a;

The following is the generated table:

name ssn date_of_birth age comments
Jen Harring 001-23-8192 1987-01-01 25 years 5 mons 13 days (null)
Tim Alekper 902-34-0340 1987-06-30 24 years 11 mons 14 days Mister 99%; AKA Vampire

PostgreSQL – Granting Access To All Functions

Now to grant all access for roles on all of the functions in the database, you can use a query such as the following:

SELECT 'GRANT ALL ON FUNCTION ' || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || ' (' || pg_get_function_identity_arguments(p.oid) || ') TO john_doe;'
FROM pg_language AS l
JOIN pg_proc AS p
ON p.prolang = l.oid
JOIN pg_namespace AS n
ON p.pronamespace = n.oid
WHERE l.lanname = 'plpgsql'

After running this query, you will have all of the GRANT statements that are needed to give the user john_doe all access to the functions in the public schema. The real key was using the pg_get_function_identity_arguments() function which is in the pg_catalog schema. I have found that it is definitely a good idea to explore that schema.