Dynamically update NEW columns in plpgsql trigger

Поиск
Список
Период
Сортировка
От Nick
Тема Dynamically update NEW columns in plpgsql trigger
Дата
Msg-id bc7f5c9c-b87c-4c57-b6b7-0801d90d96d5@s24g2000pri.googlegroups.com
обсуждение исходный текст
Ответы Re: Dynamically update NEW columns in plpgsql trigger  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: Dynamically update NEW columns in plpgsql trigger  (Dmitriy Igrishin <dmitigr@gmail.com>)
Список 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?

В списке pgsql-general по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: line CASE statemelnt in query a la Oracle
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Dynamically update NEW columns in plpgsql trigger