Re: Dynamically update NEW columns in plpgsql trigger
| От | Dmitriy Igrishin |
|---|---|
| Тема | Re: Dynamically update NEW columns in plpgsql trigger |
| Дата | |
| Msg-id | AANLkTi=Fz-0QPDTJL6EPnT-PsBxbLprWDJ9nCmg=gtE3@mail.gmail.com обсуждение исходный текст |
| Ответ на | Dynamically update NEW columns in plpgsql trigger (Nick <nboutelier@gmail.com>) |
| Список | pgsql-general |
Hey Nick,
You may do it with PL/pgSQL more easily with hstore module.
Please, refer to http://www.postgresql.org/docs/9.0/static/hstore.html
Please, look at the hstore(record) and populate_record(record, hstore)
function. Hope this helps.
And I think it will be more faster then you solution.
Regards,
Dmitriy
You may do it with PL/pgSQL more easily with hstore module.
Please, refer to http://www.postgresql.org/docs/9.0/static/hstore.html
Please, look at the hstore(record) and populate_record(record, hstore)
function. Hope this helps.
And I think it will be more faster then you solution.
Regards,
Dmitriy
2010/9/9 Nick <nboutelier@gmail.com>
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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
В списке pgsql-general по дате отправления: