Passing arrays as arguments in PL/PgSQL functions

Just a fast tip. You can use arrays as arguments in PL/PgSQL functions like this:

CREATE FUNCTION my_function(array1 int[], array2 int[])
...

And then, when you need to call it, remember you can construct your array from a string, like this:

SELECT my_function('{1, 2, 3}', '{4, 5, 6}');

You can also construct your array from a SELECT query. Just be sure to match data types:

SELECT my_function(array(SELECT id FROM my_table), '{4, 5, 6}');

How to share a sequence between two (or more) tables in MySQL

With this one I got really proud, hope it is useful for anyone. I had to share a sequence of IDs between Table_A and Table_B, so I didn’t have the column id with repeated values on these two tables. Obviously, auto_increment wouldn’t be the solution. If MySQL had supported sequences, like PostgreSQL, things would be so much easier, but it doesn’t, sadly.

I created a Sequence table (with just one row) and triggers for Table_A and Table_B that would increment the value on the Sequence row and use that value to insert.

Here is the code of creation of these tables and triggers. Really cool :)

CREATE TABLE Sequence (
	number BIGINT UNSIGNED NOT NULL
);

CREATE TABLE Table_A (
	id SERIAL PRIMARY KEY,
	other_field TEXT
);

CREATE TABLE Table_B (
	id SERIAL PRIMARY KEY,
	other_field TEXT
);

DELIMITER //
CREATE TRIGGER trigger_id_A BEFORE INSERT ON Table_A 
	FOR EACH ROW BEGIN
		DECLARE incremented_number BIGINT UNSIGNED;
		UPDATE Sequence SET number = number + 1;
		SELECT number INTO incremented_number
			FROM Sequence;
		SET new.id := incremented_number;
	END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER trigger_id_B BEFORE INSERT ON Table_B 
	FOR EACH ROW BEGIN
		DECLARE incremented_number BIGINT UNSIGNED;
		UPDATE Sequence SET number = number + 1;
		SELECT number INTO incremented_number
			FROM Sequence;
		SET new.id := incremented_number;
	END//
DELIMITER ;

-- This will start the sequence with 1 (after incrementing 0)
INSERT INTO Sequence (number) VALUES (0);

-- This will insert a row on Table_A with id 1
INSERT INTO Table_A (other_field) VALUES ('This must have ID 1');

-- This will insert a row on Table_B with id 2
INSERT INTO Table_B (other_field) VALUES ('This must have ID 2');

Adding/subtracting a variable number of seconds/any time interval to/from a timestamp/date on Postgresql

If you need to add a time interval to a timestamp or date data, using a variable, then you will have only to multiply the variable (that is, a number of unities) by the correct interval, and use the appropriate operator (+/-). The result will be a timestamp, as you can see on the documentation. It’s very simple indeed, and I’ll exemplify below.

In my situation, I have a timestamp column date_time on the photo, and want to select this value, plus a number_of_seconds (variable), of the row with id 1. Here is my SQL query:

SELECT p.date_time + number_of_seconds * INTERVAL '1 second'
	FROM photo p 
	WHERE p.id = 1;

The key here is that I added number_of_seconds * INTERVAL '1 second', and not INTERVAL 'number_of_seconds seconds', which would not work, because the PostgreSQL wouldn’t be able to correctly form the time interval.

In my table, the initial value of p.date_time was “2005-10-08 17:35:05”. Using 300 as the value in the number_of_seconds variable, the result was “2005-10-08 17:40:05”, which is the correct value that I was looking (300 seconds = 5 minutes).

Getting the intersection between two SQL queries with Postgres

I’m writing a function in Postgres, and had to get the intersection between two queries. Basically, I have an Event_Person table, with the attributes event_id and person_id, and have to find all the person_ids that occur with event_ids 100 and 101, for instance.

I had never used the INTERSECT operator, and had forgotten about it. Basically, it allows you to get the intersection between two SQL queries, and that was my case (to get the intersection between the set with event_id 100, and the one with event_id 101).

So here is the code of my full SQL query:

SELECT EP.person_id
FROM Event_Person EP
WHERE EP.event_id = 100

INTERSECT

SELECT EP.person_id
FROM Event_Person EP
WHERE EP.event_id = 101

Very simple, isn’t it? :)

Encoding PHP Objects Into JSON

Encoding a PHP object into JSON representation should be as simple as using the object as a parameter to the function json_encode, but it isn’t. According to PHP’s manual (here), the value to be encoded is allowed to be of any type, except a resource.

But to accomplish what I was trying to do (encode a PHP object into JSON), I used a function to convert an object to array (found here), and then encoded the array to JSON representation.

Function object_to_array($var) (found here):

/**
 * Convert an object into an associative array
 *
 * This function converts an object into an associative array by iterating
 * over its public properties. Because this function uses the foreach
 * construct, Iterators are respected. It also works on arrays of objects.
 *
 * @return array
 */
function object_to_array($var) {
    $result = array();
    $references = array();

    // loop over elements/properties
    foreach ($var as $key => $value) {
        // recursively convert objects
        if (is_object($value) || is_array($value)) {
            // but prevent cycles
            if (!in_array($value, $references)) {
                $result[$key] = object_to_array($value);
                $references[] = $value;
            }
        } else {
            // simple values are untouched
            $result[$key] = $value;
        }
    }
    return $result;
}

So I wrote a class called json_encodable, that includes the above function, and returns the JSON representation of the array we obtained.

Class json_encodable:

class json_encodable {
	
	public function get_json() {
		return json_encode($this->object_to_array($this));
	}
}

And, then, I made every class that I wanted to be “JSON encodable” to extend json_encodable, and, therefore, to have the get_json() method.

Then, every instance of Class1, Class2 or Class3, would have the get_json() method.

Oh, just another thing! Remember to let the attributes in Class1, Class2 and Class3 accessible to json_encodable, setting their visibility to protected or public.

How to show/hide a table row with Javascript

This is a simple tip, but I struggled with this situation for some hours and found a solution by accident.

In my code, I wanted to, by clicking on a link, show a hidden row in a table, and hide a current visible one. I used the CSS display property to hide the hidden row.

Most of the solutions around involved the dynamic creation of elements through the createElement and appendChild Javascript methods, but those were not simple enough and would bring me more trouble with ordering the table children (so to show my row on the exact position I wanted).

My solution was much simpler, just setting the display of the element to none when wanting to hide it, and to "" (empty) to show it. This did the trick, because I was trying to set it to block, inline-block, etc, and it was never showing the row properly.

The HTML code was like this:

<table>
    <tr>
        <th>Header Cell 1</th>
        <th>Header Cell 2</th>
    </tr>
    <tr id="row1">
        <td>Bla 1</td>
        <td>Bla 2</td>
    </tr>
    <tr id="row2" style="display: none;">
        <td colspan="2">Secret row, spooky</td>
    </tr>
    <tr>
        <td>This row</td>
        <td>will never hide</td>
    </tr>
</table>
<a href="javascript: void(0);" 
    onclick="showRow('row2'); hideRow('row1');">Show/Hide rows</a>

We identified the rows to show/hide with IDs, so it is easy to access their DOM elements, and set the display property on the showRow(rowId) and hideRow(rowId) functions on Javascript:

function showRow(rowId) {
    document.getElementById(rowId).style.display = "";
}
function hideRow(rowId) {
    document.getElementById(rowId).style.display = "none";
}

The beginning

Hi there, folks!

This is my uber-super brand new blog, on which I’ll try to post everything related to my experiences in the computer world, including some tips to solve difficulties that I face on my projects – maybe serving as help to people that search for similar stuff on Google.

In fact, today is holiday for the Programmers’ Day, that marks the 256th day of each year – the number 0xFF day in hexadecimal, chosen by frisky nerds, undoubtedly. Not a real holiday, though. At least not in Brazil, because I’m writing this at my research lab in college, not on the beach.