Yesterday, while trying to figure out the best way to deal with PostgreSQL arrays in PHP, I came across the new str_getcsv() function in PHP as of 5.3. This function works much the same as fgetcsv to parse a CSV line, except that it works on a string instead of a file.

For quick reference, CSV looks like this:

"My Name",14,"32,000","2009-04-15"

And the return value of a PostgreSQL array looks like this:

{"My Name",14,"32,000","2009-04-15"}

Notice the similarities?

We can use PHP’s trim and str_getcsv to turn this PostgreSQL array into a PHP array:

<?php
    $data = str_getcsv(trim($record->value, "{}"));
?>

Simple as simple does. As long as your array has only a single dimension. If you’re using multidimensional arrays in PostgreSQL then you’re dead to me.

While building MySQL database support into the OpenAvanti PHP framework, I came across an interesting quirk in MySQL that I thought must be a bug. Apparently, after submitting a bug report and getting a response from MySQL, it’s a documented feature (and later, referred to as a limitation).

Continue Reading →

This snippet courtesy of Kieran Smith.

Want to get more work done in a single query? Tired of looping through query results to simply build a list of data. How about this?

SELECT
array_to_string(
    ARRAY(
        SELECT name
        FROM projects
        WHERE customer_id = 10
        ORDER BY LOWER(name)
    ), ', '
) AS projects;

What you are doing is building an array from the subquery and then turning it back into a string immediately, so you can display multiple results in a single field. Our results might look something like this:

gnucashweb, Hangar, OpenAvanti, tarmac

Nice!

Most of my database tables always have four columns in common:

created_on timestamp
created_by_id int
last_modified_on timestamp
last_modified_by_id int

These columns should be pretty self explanatory. Usually created_on has a default of now() to auto populate it. Populating the last_modified_on column is a little more difficult, especially if you don’t want it to be populated on INSERT (which is what using default now()) would do.

Of course we could just provide for it in our code, especially if using a trusty application framework that takes care of it for us, but I like putting basic logic in the database, and we can’t assume all data updates will come from our application. They could, for example, come from a command line update.

I crafted this quick little database trigger function for taking care of this for me:

CREATE OR REPLACE FUNCTION auto_update_last_modified()
  RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
	NEW.last_modified_on:= now();
 
	RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql';

This function simply populates the last_modified_on column of NEW (the new record being updated) with the current timestamp. Now I can add a trigger to every table that has this column:

CREATE TRIGGER auto_update_table_name_last_modified
  BEFORE UPDATE
  ON table_name
  FOR EACH ROW
  EXECUTE PROCEDURE auto_update_last_modified();

And now when updating a record in table_name, it will automatically populate a value for last_modified_on.

Another cool feature I’ve recently discovered in PostgreSQL, although one available and documented for a long time, is table inheritance, which works almost exactly like object inheritance in object oriented programming. This simply means that you can have tables inheriting columns from parent tables.

Continue Reading →

I recently came across an amazing feature in PostgreSQL: the RETURNING clause. This clause allows you to return specific data as the result of an INSERT or UPDATE query.

Often, after doing one of these queries, I’m interested in getting some data, such as an autogenerated primary key field, or maybe some defaulted data (like a created timestamp). Normally, one would have to do an additional query to get this data. Using RETURNING, we can do our data manipulation and retrieval in the same query.

Example:

-- Return just the ID:
 
INSERT INTO books(name, author) VALUES('Awesome Book', 'Smart Author') 
    RETURNING book_id;
 
-- Return more than one column:
 
INSERT INTO books(name, author) VALUES ('Sweet Book', 'Other Author') 
    RETURNING book_id, created_on;
 
-- Return all the columns:
 
INSERT INTO books(name, author) VALUES ('Silly Book', 'Airhead Author') 
    RETURNING *;

In our code, we can simply treat the return value of the INSERT or UPDATE code like we would a SELECT statement.

Some things to keep in mind, though:

1. If the UPDATE query affects multiple rows, multiple rows of data will be returned. If no rows are affected, none will be returned.
2. Normally the database would return the number of rows affected, but obviously we’re overriding the return value of the query. Obviously we can check the number of rows returned by the query (like we would with a SELECT) to get around this.

Gentoo has taken a long time to unmask PostgreSQL 8.3 in Portage. Currently, the best version you can get is. 8.0.5, which, if you can tell, is pretty far away from 8.3.

This masking is largely due to the painful process of upgrading PostgreSQL (which usually involves dumping all your databases and restoring them afterwards). Here’s what I did to get 8.3 up and running.

First, make sure you backup all your databases:

pg_dumpall > postgres-backup.dump

Run the following commands as root. This will add entries to your /etc/portage/package.keywords file:

echo "dev-db/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "dev-db/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-base ~amd64" >> /etc/portage/package.keywords

Make sure to replace amd64 with your actual architecture.

Now, we’re going to unemerge PostgreSQL so we can emerge the new version:

emerge --unmerge dev-db/postgresql dev-db/libpq

Emerge the new version, configure it, start it, and add it to startup:

emerge virtual/postgresql-base virtual/postgresql-server
emerge --config =dev-db/postgresql-server-8.3.5
/etc/init.d/postgresql-8.3 start
rc-update add postgresql-8.3 default

Make sure to replace the version above with the one you actually installed. Now restore your databases:

psql -U postgres -f postgres-backup.dump template1

And you should be all set.

Helpful Links:

PostgreSQL: Backup and Restore