From: | Nick <nboutelier(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dynamically update NEW columns in plpgsql trigger |
Date: | 2010-09-09 04:12:11 |
Message-ID: | bc7f5c9c-b87c-4c57-b6b7-0801d90d96d5@s24g2000pri.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I need to dynamically update NEW columns. Ive been inserting the NEW
values into a temp table, updating them, then passing the temp table
values back to NEW (is there a better way?). Ive had success with this
method unless there is a null value...
EXECUTE 'CREATE TEMP TABLE new AS SELECT $1.*' USING NEW;
EXECUTE 'UPDATE new SET ' || dyn_col_name || ' = 1';
EXECUTE 'SELECT * FROM new' INTO NEW;
EXECUTE 'DROP TABLE new';
This last line...
EXECUTE 'SELECT $1.' || dyn_col_name INTO val USING NEW;
gives the ERROR: could not identify column "col_one" in record data
type.
However RAISE EXCEPTION '%',NEW.col_one;
returns "1" correctly.
If col_one does does not start out as a null value, then everything
works. Why does the passing from temp table back to NEW lose the USING
functionality?
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2010-09-09 05:05:42 | line CASE statemelnt in query a la Oracle |
Previous Message | Craig Ringer | 2010-09-09 03:32:37 | Re: postgresql cluster on SAN |