functions to transition Mysql SQL to PostgreSQL

[confguru]

functions to transition Mysql SQL to PostgreSQL

functions to transition Mysql SQL to PostgreSQL
http://www.xach.com/aolserver/mysql-to-postgresql.html

[sql]
--
-- Some functions to transition Mysql SQL to PostgreSQL
--
-- Created: 2000-11-01
-- Last updated: 2001-04-10
--
-- Zachary Beane <[email protected]>
--
-- This file is in the public domain.
--


drop function ifnull (text, text);
create function ifnull (text, text) returns text AS '
select coalesce($1, $2) as result
' language 'sql';


drop function ifnull (int4, int4);
create function ifnull (int4, int4) returns int4 as '
select coalesce($1, $2) as result
' language 'sql';


--
-- from_unixtime
--
-- Takes a seconds-since-the-epoch integer and returns a timestamp
--

drop function from_unixtime(integer);
create function from_unixtime(integer) returns timestamp as '
select timestamp($1) as result
' language 'sql';


--
-- unix_timestamp
--
-- Takes a timestamp and returns the seconds-since-the-epoch for it
--

drop function unix_timestamp(timestamp);
create function unix_timestamp(timestamp) returns integer as '
select date_part(''epoch'', $1)::int4 as result
' language 'sql';


--
-- to_days
--
-- Convert a timestamp to an integer representing a day count
--

drop function to_days(timestamp);
create function to_days(timestamp) returns integer as '
select date_part(''day'', $1 - ''0000-01-01'')::int4 as result
' language 'sql';


--
-- from_days
--
-- Convert a day count returned from from_days to a timestamp
--

drop function from_days(integer);
create function from_days(integer) returns timestamp as '
select ''0000-01-02''::timestamp + ($1 || '' days'')::interval as result
' language 'SQL';


--
-- convert_date_format
--
-- Convert mysql's date_format string to a postgresql to_char compatible string
--
-- BE WARNED! If your date format string contains valid to_char
-- substitutions, you will get unexpected results. For example:
--
-- test=# select date_format(now(), '%Y-%m-%d is today.');
-- date_format
-- ----------------------------
-- 2001-04-10 is totuesday .
-- (1 row)
--
-- Since the string "day" is substituted by to_char, it puts today's
-- day name into the output string.
--

drop function convert_date_format(text);
create function convert_date_format(text)
returns text
as '
set old_format $1
array set substitutions {%% %
%M Month
%W Day
%D FMDDth
%Y YYYY
%y YY
%X ""
%x ""
%a Dy
%d DD
%e FMDD
%m MM
%c FMmm
%b Mon
%j DDD
%H HH24
%k FMHH24
%h HH12
%I HH12
%l FMHH12
%i MI
%r {HH12:MI:SS AM}
%T HH24:MI:SS
%S SS
%s SS
%p AM}


# Iterate through characters of $old_format and replace any % escapes.

set string_size [string length $old_format]
set i 0
set new_format ""

while { $i < $string_size } {
set fchar [string index $old_format $i]
if { $fchar == "%" } {
set code [string range $old_format $i [expr $i + 1]]
if [info exists substitutions($code)] {
append new_format $substitutions($code)
incr i
} else {
append new_format $fchar
}
} else {
append new_format $fchar
}

incr i
}
return $new_format
' language 'pltcl';


--
-- date_format
--
-- Produce pretty output for a timestamp
--

drop function date_format(timestamp, text);
create function date_format(timestamp, text)
returns text
as '
select to_char($1, convert_date_format($2))
' language 'sql';

[/sql]