Обсуждение: Test inserted text in trigger (arrays, custom types) (corrected)

Поиск
Список
Период
Сортировка

Test inserted text in trigger (arrays, custom types) (corrected)

От
Arda Çeşmecioğlu
Дата:
Hello people, again.

Sorry, the code in the last message was wrong. Below is the corrected code:

DROP TABLE IF EXISTS mytable;
DROP TYPE IF EXISTS mytype;

CREATE TYPE mytype AS (
     a_nr Numeric(18,7),
     a_text Text
);

CREATE TABLE mytable(
     id Serial NOT NULL PRIMARY KEY,
     some_col mytype[] NOT NULL
);

CREATE OR REPLACE FUNCTION chk_mytab_input() RETURNS TRIGGER
LANGUAGE 'plpgsql'
VOLATILE
AS $BODY$
BEGIN
     -- for all some_col
     FOR c IN array_lower(NEW.some_col,1)..array_upper(NEW.some_ col,1)
LOOP
         RAISE INFO '%', (NEW.some_col[c]).a_text = 'VA';
     END LOOP;
     RETURN NEW;
END $BODY$;

CREATE TRIGGER trig_chk_mytab_input BEFORE INSERT OR UPDATE
     ON mytable FOR EACH ROW
     EXECUTE PROCEDURE chk_mytab_input();

and then when I insert some data with

INSERT INTO mytable VALUES (
     DEFAULT,
     '{
         "(55, VA)",
         "(1000, VA)"
     }'
);

I get the following output (the"RAISE INFO ..." statement):

INFO: f
INFO: f

but I expect all "t"s. So this means (I think) the VA in "(55, VA)"
statement is not the same with the VA in " RAISE INFO '%',
(NEW.some_col[c]).a_text = 'VA' " . But why?

Thanks in advance.

Re: Test inserted text in trigger (arrays, custom types) (corrected)

От
Merlin Moncure
Дата:
On Fri, Feb 24, 2012 at 4:00 AM, Arda Çeşmecioğlu <arda.mtb@gmail.com> wrote:
> Hello people, again.
>
> Sorry, the code in the last message was wrong. Below is the corrected code:
>
> DROP TABLE IF EXISTS mytable;
> DROP TYPE IF EXISTS mytype;
>
> CREATE TYPE mytype AS (
>    a_nr Numeric(18,7),
>    a_text Text
> );
>
> CREATE TABLE mytable(
>    id Serial NOT NULL PRIMARY KEY,
>    some_col mytype[] NOT NULL
> );
>
> CREATE OR REPLACE FUNCTION chk_mytab_input() RETURNS TRIGGER
> LANGUAGE 'plpgsql'
> VOLATILE
> AS $BODY$
> BEGIN
>    -- for all some_col
>    FOR c IN array_lower(NEW.some_col,1)..array_upper(NEW.some_ col,1) LOOP
>        RAISE INFO '%', (NEW.some_col[c]).a_text = 'VA';
>    END LOOP;
>    RETURN NEW;
> END $BODY$;
>
> CREATE TRIGGER trig_chk_mytab_input BEFORE INSERT OR UPDATE
>    ON mytable FOR EACH ROW
>    EXECUTE PROCEDURE chk_mytab_input();
>
> and then when I insert some data with
>
> INSERT INTO mytable VALUES (
>    DEFAULT,
>    '{
>        "(55, VA)",
>        "(1000, VA)"
>    }'
> );
>
> I get the following output (the"RAISE INFO ..." statement):
>
> INFO: f
> INFO: f
>
> but I expect all "t"s. So this means (I think) the VA in "(55, VA)"
> statement is not the same with the VA in " RAISE INFO '%',
> (NEW.some_col[c]).a_text = 'VA' " . But why?

The problem is in your literal record syntax.  you inserted a space
before 'VA', so the array value is actually ' VA'.  Prefer doing it
like this:

INSERT INTO mytable VALUES (
   DEFAULT,
   array[
    row(55, 'VA'),
    row(1000, 'VA')
   ]::mytype[]
);

to avoid these types of problems.

merlin