PostgreSQL – Select PL/pgSQL Function Definitions

Posted on May 9, 2012 at 4:56 PM by Chris West

Something that I did before, but never documented until now was querying the database for all of the PL/pgSQL function definitions. In PostgreSQL, you can run the following to pull all of them along with the owner, schema, function name, and function definition:

SELECT a.rolname AS "owner",
  n.nspname AS "schema",
  p.proname AS "name",
  pg_get_functiondef(p.oid) AS "definition"
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
JOIN pg_authid AS a
ON a.oid = p.proowner
WHERE l.lanname = 'plpgsql'
ORDER BY "schema" ASC,
  "name" ASC

Of course, you can modify the above query so that you can search for whatever you want based on all of the retrieved fields. If you are still learning about PostgreSQL, I definitely suggest looking through all of the tables, views, and functions in the pg_catalog schema.

Regular Expression Examples

Posted on May 1, 2012 at 4:09 PM by Chris West

One of the things that I love about string manipulation is the existence of regular expressions. For this reason, I have decided to share a few examples that may help those who are learning about regular expressions so that can understand them a bit better.

JavaScript – General Variable Name

In many languages, a variable must start off with a letter and may be followed by letters, numbers, and/or the underscore character. Knowing this, we could use the following regular expression in JavaScript to match a variable name:

var expVarIFlag = /^[A-Z]\w*$/i;

Basically, the above regular expression will only match a string if it matches the pattern for a variable name. The reason that I only put [A-Z] and not [A-Za-z] is because in JavaScript you can specify an “i” flag after the regular expression which indicates that the expression will be case-insensitive. Another thing to note is that I used the \w class which basically represents a word. A word in regular expressions typically means any letter (A to Z regardless of case), any digit (0 to 9), or the underscore character. The reason I used the asterisk instead of the plus sign is because a variable may be just one letter.

NOTE: Although this regular expression may work for other languages, in JavaScript, a variable name can also start off with an underscore or dollar sign.

PostgreSQL – Date (MM/DD/YYYY)

Even though using a regular expression shouldn’t be the way to completely validate a date, you can do so partially with the following in PostgreSQL:

SELECT id, text
FROM answers
WHERE text ~ '^(0\\d|1[012])/([012]\\d|3[01])/\\d{4}$';

The above query will pull all of the answers with a text that matches the pattern to see if it looks like a valid date.

First it specifies that the first two characters are a 0 followed by another digit or a 1 followed by either a 0, 1, or 2.
Next should come a forward slash.
Next should be either…

0, 1, or 2 followed by any digit
or 3 followed by a 0 or 1

Finally should be another forward slash followed by four digits.

One thing to notice is that in order to properly escape the class inside of a string (which is what we have to do here in PostgreSQL), you have to escape the backslash so that it will be interpreted as one backslash in front of the next character thus rendering “\\w” as “\w“.

PHP – Hexadecimal Color Code

In CSS, a color code can be in many different forms. One accepted form is hexadecimal. The hex form can be three characters or six characters long. It can start off with a number sign, but this symbol isn’t required. Knowing all of this, we could use the following in PHP to validate the hex color:

$pattern = '/^#?([0-9A-F]{3}){1,2}$/i';
$validHex = preg_match($pattern, $_GET['hex']);

The preg_match() function is used to validate the GET parameter called “hex” against our regular expression:

First it specifies that the first character may be a number sign (#).
Next I have defined a parenthesized group which matches any three hexadecimal digits.
After that, I am specifying that my parenthesized group pattern may appear once or two times in a row and that no other characters should follow.
Finally, you will notice that I am again using the “i” flag to indicate that this is a case-insensitive pattern.

Python – Simple Image File Names

Let’s use Python now to check to see if a file name looks like a valid image name:

# Import the regular expression library
import re

# Defining the compiled regular expression.
pat = "^[^/\\?%*:|\"<>]+\\.(jpg|png|gif|bmp)$"
reImg = re.compile(pat, re.I)

# Getting the file name from the user
fileName = raw_input("File name:  ")

# Determine if the file name is an image name
isImage = reImg.match(fileName) is not None

The regular expression created does the following:

First makes sure that the string starts off with one more characters which are none of the following: / \ ? % * : | “ < >
In the end it checks that a dot is found followed by one of the following extensions which must appear at the end of the string:

It is also important to note that by using “re.I“, I specified that casing would be ignored.

The code should basically prompt the user for a file name and then validate the string entered to determine if it matches the regular expression for an image. The boolean value indicating whether or not it is an image is stored in the isImage variable.

VBScript – Format Large Integer With Commas

The following is how you could use a regular expression to insert commas into a number (integer):

' Setup the RegExp for testing if input is an integer.
Dim re : Set re = new RegExp
re.Pattern = "^(0|-?[1-9]\d*)$"

' Get the input integer from the user.
input = InputBox("Enter an integer", "Your Integer", 123456789)

' If the input is an integer...
If re.Test(input) Then
  ' Modify the pattern to input the commas correctly.
  re.Pattern = "(\d)(?=(\d{3})+$)"
  re.Global = True

  ' Reformat the integer, if given.
  newInput = re.Replace(input, "$1,")

  ' Display the input formatted with commas.
  MsgBox input & " became " & newInput

' If the input is not an integer, tell the user so.
Else
  MsgBox "The input given wasn't recognized as an integer."
End If

The first regular expression basically tests to make sure that the input is either simply a zero or one or more digits with the first one being non-zero. In other words, the first pattern makes sure that the input is an integer that doesn’t start with a zero (unless it is zero). The second regular expression is what is used to insert the comma(s) in the right place(s). It finds every instance in which one digit is followed by at least one group of three digits. By starting the group off with “?=” I am ensuring that the matched group will not be skipped on the next pass through.

PostgreSQL – Example Query As A Function

Posted on April 12, 2012 at 2:40 PM by Chris West

One of the things that I am not all that thrilled about is the fact that at times it is hard to find examples of PostgreSQL queries or plpgsql functions. For this reason, I want to help out anyone who would like to see an example of converting a query into a function:

CREATE OR REPLACE FUNCTION get_user_credentials(
  IN i_user_ids INTEGER[],
  OUT o_id users.id%TYPE,
  OUT o_username users.username%TYPE,
  OUT o_first_name users.first_name%TYPE,
  OUT o_last_name users.last_name%TYPE,
  OUT o_email_address users.email_address%TYPE,
  OUT o_credentials TEXT
) RETURNS SETOF RECORD
  VOLATILE
AS $$
BEGIN
  RETURN QUERY
    SELECT id,
      username,
      first_name,
      last_name,
      email_address,
      ARRAY_TO_STRING(ARRAY(
        SELECT c.name
        FROM credentials AS c
        JOIN user_credentials AS uc
        ON c.id = uc.credential_id
        AND NOT uc.is_deleted
        AND uc.user_id = users.id
      ), ', ')
    FROM users
    WHERE id = ANY(i_user_ids)
    AND NOT is_deleted;
END;
$$ LANGUAGE plpgsql;

This function would work assuming that you have the following tables defined:

users

id – INTEGER
username – VARCHAR
first_name – VARCHAR
last_name – VARCHAR
email_address – VARCHAR
is_deleted – BOOLEAN

credentials

id – INTEGER
name (something like admin, backend, frontend, etc.) – VARCHAR

user_credentials

user_id – INTEGER
credential_id – INTEGER
is_deleted – BOOLEAN

The following would be an example call to this function that can return two lines if there are users with the ID of 1 and 5:

SELECT *
FROM get_user_credentials(ARRAY[1,5]);

PostgreSQL – Split Array To Multiple Rows

Posted on March 27, 2012 at 6:00 PM by Chris West

Today, I was asked how to select each element in an array as a separate row. In other words, I needed to convert an array into rows (one for each array element). Below is the right way to do it by using the built-in unnest() function (requires PostgreSQL 8.4+):

SELECT UNNEST(ARRAY['Julio','César','de','León']) AS names

If you don’t have PostgreSQL 8.4+ available to you, the following solution which was inspired by this page will produce the same results (requires PostgreSQL 8.0+):

SELECT arr[i]
FROM (
  SELECT generate_series(1, array_upper(arr, 1)) AS i, arr
  FROM (SELECT ARRAY['Julio','César','de','León'] arr) t
) t

Personally, if you will need to split (or explode) an array into rows, it is better to create a quick function that would do this for you. Again, I don’t claim to be a PostgreSQL guru, therefore if you know of a built-in way of doing this in a version of PostgreSQL before 8.4, please let me know.

PostgreSQL – Granting Access To All Tables

Posted on March 23, 2012 at 6:00 PM by Chris West

Recently, since the DBA wasn’t available, I had to assume his duties and grant SELECT privileges to a user (let’s say john_doe) for all of the tables in the public schema. After quite a bit of research (Google of course), I learned about the pg_tables view. After realizing that this view gives you the ability to see all of the tables and their respective schema names, I was able to write a query such as the following to accomplish my task:

SELECT 'GRANT SELECT ON "' || schemaname || '"."' || tablename || '" TO john_doe;'
FROM pg_tables
WHERE schemaname = 'public';

After running this query, I was given all of the GRANT statements that I needed to run to give the user john_doe read access to all of the tables in the public schema. The beautify of this type of query as that it is short and modify in order to get the desired results. Of course, you can use your imagination to think of countless other reasons why knowing about this view is advantageous.

Chris West's Blog

Programming, math, and more.

Tag Archives: PostgreSQL