About array in PlPgsql trigger function

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