On Wed, Mar 08, 2006 at 09:16:54AM -0500, Emi Lu wrote:
> In PostgreSQL 8.0.1 (plpgsql), I was trying to use array in the trigger
> function.
>
> DECLARE
> clear_id_colValueArr VARCHAR[100];
> BEGIN
> clear_id_colValueArr[1] := NEW.clear_id1;
> clear_id_colValueArr[2] := NEW.clear_id2;
> clear_id_colValueArr[3] := NEW.clear_id3;
> ....
> clear_id_colValueArr[100] := NEW.clear_id100;
> ...
> END;
Ugh...having a hundred columns with names like clear_idN is a hint
to think about whether this is the best design.
> I always get NULL for clear_id_colValueArr.
In earlier versions prior to 8.0.2 you'll need to initialize the
array before using it:
clear_id_colValueArr VARCHAR[100] := '{}';
> Also, I tried to run raise notice '%', clear_id_colValueArr[0],
> I got an compile error.
This could be due to a couple of things. Your example doesn't show
if the function body is in dollar quotes; if not then strings inside
the function need to be delimited with pairs of single quotes (''%'').
More importantly, in versions prior to 8.1 RAISE doesn't understand
expressions like clear_id_colValueArr[0]; you can get around this
limitation with a temporary variable:
tmp := clear_id_colValueArr[0];
RAISE NOTICE '%', tmp;
(Incidentally, your code doesn't show [0] being assigned.)
> Also, is there a way that I can get NEW.ColValues by specifying column
> number but not NEW.ColumnName?
Not in PL/pgSQL, but you can do this in other languages like PL/Perl,
PL/Tcl, PL/Python, PL/Ruby, etc.
--
Michael Fuhr