Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: test inserted text in trigger (arrays, custom types)

    Hello people,

    I tried to compose a header that sums up the question .

    Now to the actual question;

    I do the following

    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?
    Last edited by arda; 02-24-12 at 05:56.

  2. #2
    Join Date
    Feb 2012
    Posts
    2

    a workaround?

    I found out that if you create the type with the ::Text column in the first place like:

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

    and then rearrange the INSERT according to this like:

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

    the problem goes away.

    Any idea why this happens?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •