From: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
---|---|
To: | Nick <nboutelier(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Dynamically update NEW columns in plpgsql trigger |
Date: | 2010-09-09 09:21:43 |
Message-ID: | AANLkTi=Fz-0QPDTJL6EPnT-PsBxbLprWDJ9nCmg=gtE3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
2010/9/9 Nick <nboutelier(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ungermann Carsten | 2010-09-09 10:55:08 | Regular expression in an if-statement will not work |
Previous Message | Pavel Stehule | 2010-09-09 06:06:37 | Re: Dynamically update NEW columns in plpgsql trigger |