Re: 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