Обсуждение: About array in PlPgsql trigger function

Hello,

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;

I always get NULL for clear_id_colValueArr. Also, I tried to run
raise notice '%', clear_id_colValueArr[0], I got an compile error.

Could someone tell me how to use array in a trigger function please?

Also, is there a way that I can get NEW.ColValues by specifying column
number but not NEW.ColumnName?

Thanks a lot,
Ying

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