Skip site navigation (1) Skip section navigation (2)

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

From: Arda Çeşmecioğlu <arda(dot)mtb(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Test inserted text in trigger (arrays, custom types) (corrected)
Date: 2012-02-24 10:00:27
Message-ID: 4F475FBB.9050301@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

Responses

pgsql-novice by date

Next:From: TanstaaflDate: 2012-02-24 14:07:18
Subject: Reliably backing up a live database
Previous:From: Arda ÇeşmecioğluDate: 2012-02-24 09:43:14
Subject: Test inserted text in trigger (arrays, custom types)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group