Обсуждение: Why does this not work?

-- Table: test
CREATE TABLE "test" (
  "test1" varchar(10)
) WITH OIDS;

-- Function: laenge()
CREATE FUNCTION "laenge"() RETURNS "opaque" AS '
begin
if length(new.test1) > 10 then
        new.test1 = substr(new.test1,1,9);
end if;
return new;
end
' LANGUAGE 'plpgsql';

-- Trigger: laenge_trig ON test
CREATE TRIGGER "laenge_trig" BEFORE INSERT OR UPDATE ON "test" FOR EACH ROW EXECUTE PROCEDURE laenge();

insert into test (test1) values  ('0123456789012');

value too long for type character varying(10)

regards






Johann Zuschlag
zuschlag@online.de

On Thu, 11 Apr 2002, Johann Zuschlag wrote:

> -- Table: test
> CREATE TABLE "test" (
>   "test1" varchar(10)
> ) WITH OIDS;
>
> -- Function: laenge()
> CREATE FUNCTION "laenge"() RETURNS "opaque" AS '
> begin
> if length(new.test1) > 10 then
>         new.test1 = substr(new.test1,1,9);
> end if;
> return new;
> end
> ' LANGUAGE 'plpgsql';
>
> -- Trigger: laenge_trig ON test
> CREATE TRIGGER "laenge_trig" BEFORE INSERT OR UPDATE ON "test" FOR
> EACH ROW EXECUTE PROCEDURE laenge();
>
> insert into test (test1) values  ('0123456789012');
>
> value too long for type character varying(10)

The value is being coerced into the type before your trigger
runs to be put into the values you're testing and it fails
at that point.

On Thu, 11 Apr 2002 10:54:42 -0700 (PDT), Stephan Szabo wrote:

>> -- Trigger: laenge_trig ON test
>> CREATE TRIGGER "laenge_trig" BEFORE INSERT OR UPDATE ON "test" FOR
>> EACH ROW EXECUTE PROCEDURE laenge();
>>
>> insert into test (test1) values  ('0123456789012');
>>
>> value too long for type character varying(10)
>
>The value is being coerced into the type before your trigger
>runs to be put into the values you're testing and it fails
>at that point.

Is there a solution for such a problem?
Let's assume you can't be sure whether you get
the appropriate length in an insert.

Thanks for your help,

regards

Johann Zuschlag
zuschlag@online.de

----- Original Message -----
From: "Johann Zuschlag"

>
> Is there a solution for such a problem?
> Let's assume you can't be sure whether you get
> the appropriate length in an insert.
>

what about changing the column from varchar to text?

size shouldn't be an issue and your trigger will be able to cut the content
to the desired length


hth,

    Marin

----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "

"Johann Zuschlag" <zuschlag@online.de> writes:
>> The value is being coerced into the type before your trigger
>> runs to be put into the values you're testing and it fails
>> at that point.

> Is there a solution for such a problem?

Don't use varchar(n) --- use text.  You can still enforce the length
limit you want inside the trigger, if you really want a length limit
at all.

            regards, tom lane

Johann Zuschlag wrote:
> On Thu, 11 Apr 2002 10:54:42 -0700 (PDT), Stephan Szabo wrote:
>
> >> -- Trigger: laenge_trig ON test
> >> CREATE TRIGGER "laenge_trig" BEFORE INSERT OR UPDATE ON "test" FOR
> >> EACH ROW EXECUTE PROCEDURE laenge();
> >>
> >> insert into test (test1) values  ('0123456789012');
> >>
> >> value too long for type character varying(10)
> >
> >The value is being coerced into the type before your trigger
> >runs to be put into the values you're testing and it fails
> >at that point.
>
> Is there a solution for such a problem?
> Let's assume you can't be sure whether you get
> the appropriate length in an insert.

    Create the table field as VARCHAR without a maximum size.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On Thu, 11 Apr 2002 14:45:06 -0400, Tom Lane wrote:

>> Is there a solution for such a problem?
>
>Don't use varchar(n) --- use text.  You can still enforce the length
>limit you want inside the trigger, if you really want a length limit
>at all.

Thanks to all, for your help. It seems it is not possible.
Ok, I go for the text solution. The only trouble is, I have
to debug my application to see what nonsense it is
trying to do. But postgresql/psqlodbc is good help doing
that.

IIRC there is no speed difference between varchar and text?

regards

Johann Zuschlag
zuschlag@online.de

On Thursday 11 April 2002 17:38, Johann Zuschlag wrote:

>   "test1" varchar(10)

> CREATE FUNCTION "laenge"() RETURNS "opaque" AS '
> begin
> if length(new.test1) > 10 then
...
> insert into test (test1) values  ('0123456789012');
>
> value too long for type character varying(10)

I believe it's the parser being helpful and checking data-types as early as
possible. You can see why - if "new.test1" is a varchar(10) how _can_ it hold
any more.

You might want to look in the mail archives. Someone mentioned this very
problem not long ago.

- Richard Huxton