Today, I went out to get some supplies that I didn't need a few daysago... funny how these things turn up.. I also found that the backdoor of my apartment leaks heat like a funnel, so, as I don't intendto use that door this winter, I put duct tape over the seam between thedoor 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 orapartment -- 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 dealingwith such a door recently, and needing to throw my weight into it to make itopen 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'tseal heat in/out at all. Damn, now I'm going to be trying to remember thatdoor for the rest of the night. Ugh.
Earlier tonight, I spent about an hour with a coworker on how to figureout how to convert between integer and interval types in Postgres. Thetypes arn't naturally compatible, and the Postgres documentation is indeedpoor on this front. I'll preserve my efforts here for anyone else googlingfor a solution (as google seems to like me).
The initial problem is that he had a table with a column created (and datafilled-in) as integer, and wanted to change it to sequence, without reloadingit. 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 scalehe 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 actuallycreated one. The documentation for CREATE CAST seems to indicate that whatwe really need to do is make a function, and CREATE CAST will simplyassociate that with automagic casting. He provides foo || " seconds" ashow intervals are normally entered via the tools we're using, and we bothstart chewing on how to implement the function.. He finds an example in thedocumentation
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 permissionsto 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 piecesin place, return to the function, and why it doesn't work. It looks to me likeI 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 thetwo.. and it looks like the funky quotes from above arn't causing evaluationof $1, and postgres is discarding the dollar sign, that's why I'm alwaysgetting 1-second intervals. So, I put $1 || \' seconds\' into theparenthesis.. and promptly get another error, that the return type istext, not sequence as declared. Ahh, so close. Can I wrap the select withanother select that fixes the type oddity? Hmm.. no. Could I grab the primarykey and create a temporary table .. that'd be too ugly. So, I have some textthat needs to be turned into an interval.. if only I could feed it throughthe interactive tool itself.. hmm.. Oh, wait, what I'm doing is making atypecast.. but presumably there's already a CAST between text and intervalthat the interactive tool uses.. can I just call it? And that's basicallyit. 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, andfind that he setup a new tool for me to see some aspects of my UML systemthat I can't easily see from the inside.. my bandwidth usage is a verysmall 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.