Today, I went out to get some supplies that I didn't need a few days ago... funny how these things turn up.. I also found that the back door of my apartment leaks heat like a funnel, so, as I don't intend to use that door this winter, I put duct tape over the seam between the door and the wall. Funny -- my place in Columbus also had inadequate doors.. I guess doors are probably a hard thing to keep working well in a home or apartment -- it's a question of tolerances, always tricky in physical systems.. go over, and the door won't shut easily.. hmm.. oddly, I seem to recall dealing with such a door recently, and needing to throw my weight into it to make it open or close properly.. but I can't remember where... .. and going under, the door opens and closes easily, but the resulting gap means that it won't seal heat in/out at all. Damn, now I'm going to be trying to remember that door for the rest of the night. Ugh.

Earlier tonight, I spent about an hour with a coworker on how to figure out how to convert between integer and interval types in Postgres. The types arn't naturally compatible, and the Postgres documentation is indeed poor on this front. I'll preserve my efforts here for anyone else googling for a solution (as google seems to like me).

The initial problem is that he had a table with a column created (and data filled-in) as integer, and wanted to change it to sequence, without reloading it. With any two directly-compatible types (e.g. going from integer to bigint), you'd just do

ALTER TABLE foo ADD COLUMN newinfo; UPDATE foo SET newinfo=info; ALTER TABLE foo DROP COLUMN info; ALTER TABLE foo RENAME COLUMN newinfo TO info;

This, of course, assumes there arn't any constraints involved on info. But, of course, integers and intervals arn't compatible. He has a scale he wants -- one numeric unit corrisponds to a second. So, we need to create a CAST for postgres. I've heard of them before, but never actually created one. The documentation for CREATE CAST seems to indicate that what we really need to do is make a function, and CREATE CAST will simply associate that with automagic casting. He provides foo || " seconds" as how intervals are normally entered via the tools we're using, and we both start chewing on how to implement the function.. He finds an example in the documentation

CREATE FUNCTION tp1 (integer, numeric) RETURNS integer AS 'UPDATE bank SET balance = balance - $2 WHERE accountno = $1; SELECT 1;' LANGUAGE SQL; and tweaks it to be: CREATE FUNCTION foo(int) RETURNS interval AS ' SELECT interval \'$1 second\ LANGUAGE SQL; Problem is, that doesn't work, always just giving us 1-second intervals, regardless of what integers we feed it. After briefly talking about permissions to types, I show my CREATE CAST and test functions:

CREATE FUNCTION secondize(integer) RETURNS interval AS 'SELECT interval \'$1 seconds\ LANGUAGE SQL; CREATE CAST (integer AS interval) WITH FUNCTION secondize(integer); SELECT CAST(10 as interval);

We test how it would be applied if it did work, and then, all the other pieces in place, return to the function, and why it doesn't work. It looks to me like I don't understand how argument handling works in this type of code snippet.. So, after much trial and error, I come up with

CREATE FUNCTION myadd(integer, integer) RETURNS integer AS 'SELECT ($1 + $2) AS RESULT' LANGUAGE SQL;

I'm almost there -- I just need to look at the differences between the two.. and it looks like the funky quotes from above arn't causing evaluation of $1, and postgres is discarding the dollar sign, that's why I'm always getting 1-second intervals. So, I put $1 || \' seconds\' into the parenthesis.. and promptly get another error, that the return type is text, not sequence as declared. Ahh, so close. Can I wrap the select with another select that fixes the type oddity? Hmm.. no. Could I grab the primary key and create a temporary table .. that'd be too ugly. So, I have some text that needs to be turned into an interval.. if only I could feed it through the interactive tool itself.. hmm.. Oh, wait, what I'm doing is making a typecast.. but presumably there's already a CAST between text and interval that the interactive tool uses.. can I just call it? And that's basically it. The solution:

CREATE FUNCTION secondize(integer) RETURNS interval AS 'SELECT CAST( ($1 || \'seconds\') AS INTERVAL) AS RESULT' LANGUAGE SQL; CREATE CAST (integer AS interval) WITH FUNCTION secondize(integer);

And it works. Woo!

Later, after a trip to Eat'n'Park, I chat with my ISP for dachte.org, and find that he setup a new tool for me to see some aspects of my UML system that I can't easily see from the inside.. my bandwidth usage is a very small fraction of what I'm actually allowed, which is good to know -- I often have nagging doubts about these kinds of things. There's other stuff to write about, but it can wait until later. Bedtime for me.