Обсуждение: Dynamically update NEW columns in plpgsql trigger

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?

Hello

plpgsql isn't good tool for this. use a plperl or plpython instead.
your solution is extremly slow.

Regards

Pavel Stehule

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
>

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

On Sep 9, 2:21 am, dmit...@gmail.com (Dmitriy Igrishin) wrote:
> Hey Nick,
>
> You may do it with PL/pgSQL more easily with hstore module.
> Please, refer tohttp://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 <nboutel...@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-gene...@postgresql.org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-general- Hide quoted text -
>
> - Show quoted text -

Thanks Dmitriy,

Im guessing that hstore(record) is not compatible with 8.4.4? I get an
error "cannot cast type record to hstore".

-Nick

On Thu, Sep 9, 2010 at 6:34 PM, Nick <nboutelier@gmail.com> wrote:
> On Sep 9, 2:21 am, dmit...@gmail.com (Dmitriy Igrishin) wrote:
>> Hey Nick,
>>
>> You may do it with PL/pgSQL more easily with hstore module.
>> Please, refer tohttp://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 <nboutel...@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-gene...@postgresql.org)
>> > To make changes to your subscription:
>> >http://www.postgresql.org/mailpref/pgsql-general- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Dmitriy,
>
> Im guessing that hstore(record) is not compatible with 8.4.4? I get an
> error "cannot cast type record to hstore".

nope...9.0.   your temp table approach is pretty horrible though --
i'd consider testing 9.0 (likely out in couple weeks) asap, or
consider another pl.  the hstore approach is better though...

merlin