Since I'm just startingto understand the wisdom of my switch from
MySQL to PostgreSQL I offer these notes for the archives in the hopes
that they'll show up in later searches:
George Johnson writes:
> Mysql has a richer set of date and time functions and easy-to-use
> conversions. PostgreSQL should have that at some point.
I just wanna get this into the archives so that the next person who
goes searching finds it. To get my code up and running fast (within
the hour) my first pass actually used calls to NOW() in the values of
the update and inserts, making the conversion from MySQL to PostgreSQL
actually have less smarts in the database:
Some triggers for created/last updated times:
CREATE FUNCTION entered_stamp () RETURNS OPAQUE AS
' BEGIN
IF NEW.entered ISNULL THEN
NEW.entered := ''now'';
END IF;
IF NEW.updated ISNULL THEN
NEW.updated := ''now'';
END IF;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE FUNCTION updated_stamp () RETURNS OPAQUE AS
' BEGIN
IF NEW.updated ISNULL THEN
NEW.updated := ''now'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER abc_entered BEFORE INSERT ON abc
FOR EACH ROW EXECUTE PROCEDURE entered_stamp();
CREATE TRIGGER abc_updated BEFORE UPDATE ON abc
FOR EACH ROW EXECUTE PROCEDURE updated_stamp();
> I don't know if postgresql has set and enumeration data types ... or
> if Array covers that.
Enum should generally be done with an extra table and a join.
Dan
