From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Arda Çeşmecioğlu <arda(dot)mtb(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Test inserted text in trigger (arrays, custom types) (corrected) |
Date: | 2012-02-24 14:33:50 |
Message-ID: | CAHyXU0zGwV0PF8tBb8VHsKfmXu9ZzWBvgvNj+b0vMo_WBNjG4g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Fri, Feb 24, 2012 at 4:00 AM, Arda Çeşmecioğlu <arda(dot)mtb(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-02-24 16:31:08 | Re: Reliably backing up a live database |
Previous Message | Daniel Staal | 2012-02-24 14:30:34 | Re: Reliably backing up a live database |