Re: unable to assign value to composite column in trigger

On Dec 18, 2008, at 4:19 PM, Ketema Harris wrote:

> The following is a snippet from a trigger i am using:
>
> _mycompositeType.user_id = (OLD).mycompositeType.user_id;
> ...do some stuff...  --notice that assigning from the trigger record
> works
>
> but later on...
>
> (new).mycompositeType.transaction_id := _transaction_id;
>
> Fails with syntax error at or near "("
>
> I have tried removing the () and it fails with "syntax error at or
> near "new""
>
> non composite column can be assigned to in the trigger just fine:
>
> new.other_columm := 5;  --gives no issues
>
> How can a composite column be assigned to inside a trigger?
>
> Thanks
>
> Ketema J. Harris
> www.ketema.net
> ketema@ketema.net
> ketemaj on iChat
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

I have answered my own question.  It appears that any composite column
elements in the NEW and OLD variables within a trigger can be read
from using the () accessor syntax, but not assigned to.  What I had to
do was create a variable of the composite type and then assign the
whole column.

EX:

_var mycompositeType;

_var.element  := 1
_var.element2 := 2

new.compositeCol := _var

It seems a little strange that the same accessor syntax can't be used
on both sides of the assignment operator, but this method solved my
problem.