Re: [DOCS] Partitioning documentation example
On Fri, 2008-01-04 at 10:27 -0500, Bruce Momjian wrote: > It seems to me it would be much clearer if we added a second example > that used to_char() to create the INSERT statement dynamically based on > NEW.logdate: > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > EXECUTE 'INSERT INTO measurement_y' || > to_char(NEW.logdate,'_mMM') || ' VALUES (NEW.*)'; > END; > $$ > LANGUAGE plpgsql; > > It will of course fail if the table does not exist, which I think is > what we want. This trigger function would not have to be modified when > new tables are added. I like your new trigger better than the old, but I am still uncomfortable with recommending we use triggers with COPY for performance reasons and we should add a caveat. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[DOCS] Partitioning documentation example
Simon, I was looking at the new table partitioning documentation that recommends triggers: http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION and came upon this trigger function example: CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; It seems to me it would be much clearer if we added a second example that used to_char() to create the INSERT statement dynamically based on NEW.logdate: CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'_mMM') || ' VALUES (NEW.*)'; END; $$ LANGUAGE plpgsql; It will of course fail if the table does not exist, which I think is what we want. This trigger function would not have to be modified when new tables are added. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] Partitioning documentation example
Bruce Momjian <[EMAIL PROTECTED]> writes: > EXECUTE 'INSERT INTO measurement_y' || > to_char(NEW.logdate,'_mMM') || ' VALUES (NEW.*)'; That won't actually work. Even if it did, I don't think we should be recommending use of EXECUTE here; the performance implications are bad. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Partitioning documentation example
Simon Riggs wrote: > On Fri, 2008-01-04 at 10:27 -0500, Bruce Momjian wrote: > > > It seems to me it would be much clearer if we added a second example > > that used to_char() to create the INSERT statement dynamically based on > > NEW.logdate: > > > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > > RETURNS TRIGGER AS $$ > > BEGIN > >EXECUTE 'INSERT INTO measurement_y' || > > to_char(NEW.logdate,'_mMM') || ' VALUES (NEW.*)'; > > END; > > $$ > > LANGUAGE plpgsql; > > > > It will of course fail if the table does not exist, which I think is > > what we want. This trigger function would not have to be modified when > > new tables are added. > > I like your new trigger better than the old, but I am still > uncomfortable with recommending we use triggers with COPY for > performance reasons and we should add a caveat. OK, I will add this example in addition to the examples already present. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] Partitioning documentation example
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > >EXECUTE 'INSERT INTO measurement_y' || > > to_char(NEW.logdate,'_mMM') || ' VALUES (NEW.*)'; > > That won't actually work. Even if it did, I don't think we should be > recommending use of EXECUTE here; the performance implications are bad. Bummer. :-( -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
