Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D2D019FA0FE for ; Thu, 2 Nov 2006 19:07:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 95083-06 for ; Thu, 2 Nov 2006 19:07:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web56002.mail.re3.yahoo.com (web56002.mail.re3.yahoo.com [216.252.110.181]) by postgresql.org (Postfix) with SMTP id 68BEF9F9EEC for ; Thu, 2 Nov 2006 19:07:00 -0400 (AST) Received: (qmail 25241 invoked by uid 60001); 2 Nov 2006 23:06:58 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type; b=Tbr385aXZOBpIL5M4hDNgO0rdH0ZR3Xxsn8r4UC41RuV71AaBfpDxlYDm3gYO3MXuRNL95fJuVTs0wd71AjlL1wsX2nnDxYGKQUyeX/0UA7M3fUpegpc1oA3Hfa5Jc7ja9iSxD8GrTk5wz7apRwd2dX1A2B1O4fYNr7n/n1Lx84= ; Message-ID: <20061102230658.25239.qmail@web56002.mail.re3.yahoo.com> Received: from [74.98.225.153] by web56002.mail.re3.yahoo.com via HTTP; Thu, 02 Nov 2006 15:06:58 PST Date: Thu, 2 Nov 2006 15:06:58 -0800 (PST) From: Sorin Schwimmer Subject: PLPGSQL question To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1277559103-1162508818=:23532" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.742 tagged_above=0 required=5 tests=BAYES_05, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200611/70 X-Sequence-Number: 26722 --0-1277559103-1162508818=:23532 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi All,=0A=0AI have a stored procedure that fails and don't know how to fix= it, so I hope to find some help here.=0A=0AI works on a table called 'loca= tions' that looks like this:=0A=0Adesign=3D# \d locations=0A = Table "public.locations"=0A Column | Type | Modifiers= =0A---------+----------------------+---------------------=0A store | char= acter(1) | not null=0A coord | character varying(8) | not null=0A= room | real | not null=0A mu | smallint = | not null=0A block | boolean | default false=0A tags |= bit varying(100) | default B'0'::"bit"=0A details | text = |=0AIndexes:=0A "locations_pkey" PRIMARY KEY, btree (store, coord)=0A= "store_coord_locations" btree (store, coord)=0ACheck constraints:=0A = "locations_room_check" CHECK (room >=3D 0.0::double precision)=0AForeign-k= ey constraints:=0A "locations_store_fkey" FOREIGN KEY (store) REFERENCES= stores(code)=0A=0A=0AAfter some work, it tries to execute the following up= date:=0A=0AUPDATE locations SET tags=3DSUBSTRING(bv,1,r),room=3Droom-$3 WHE= RE coord=3D$2 AND store=3D$1;=0A=0Aand the variables, shown by=0A=0Araise n= otice '% % % % % %',bv,r,$3,$2,$1,data.room;=0A=0Alook like this:=0A=0ANOTI= CE: 1110110000111000000...0 13 4 BK1B =F7 4.58=0A=0AThe first value is a bi= nary string, 96 bits long, the tail are just zeroes, so I cut it.=0A=0AThe = initial values in the table are:=0A=0Adesign=3D# select * from locations wh= ere coord=3D'BK1B' and store=3Dchr(247);=0A store | coord | room | mu | blo= ck | tags | details=0A-------+-------+------+----+-------+--------= -------+---------=0A =F7 | BK1B | 4.58 | 1 | f | 1010110000111 | = D=0A(1 row)=0A=0AAnd now the error:=0A=0AProgrammingError: ERROR: new row f= or relation "locations" violates check constraint=0A"location_room_check"= =0Aand then the context=0A=0A=0AIf I try to make the update manualy, works = flawless (as 4.58-4 >=3D 0.0).=0A=0AFor now I droped the constrained, but h= ow can I solve it?=0A=0AThanks,=0ASorin=0A=0A=0A=0A --0-1277559103-1162508818=:23532 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi All,

I have a stored procedure that fails an= d don't know how to fix it, so I hope to find some help here.

I work= s on a table called 'locations' that looks like this:

design=3D# \d = locations
          &n= bsp;    Table "public.locations"
 Column  |&nbs= p;        Type    &n= bsp;    |      Modifiers
-------= --+----------------------+---------------------
 store   = | character(1)         | not null coord   | character varying(8) | not null
 room&n= bsp;   | real           &nbs= p;     | not null
 mu    &n= bsp; | smallint          =    | not null
 block   | boolean  &nb= sp;           | default f= alse
 tags    | bit varying(100)   &n= bsp; | default B'0'::"bit"
 details | text    &= nbsp;            |Indexes:
    "locations_pkey" PRIMARY KEY, btree (store= , coord)
    "store_coord_locations" btree (store, coord)=
Check constraints:
    "locations_room_check" CHECK (= room >=3D 0.0::double precision)
Foreign-key constraints:
 &n= bsp;  "locations_store_fkey" FOREIGN KEY (store) REFERENCES stores(code)


After some work, it tries to execute the following= update:

UPDATE locations SET tags=3DSUBSTRING(bv,1,r),room=3Droom-$= 3 WHERE coord=3D$2 AND store=3D$1;

and the variables, shown by
raise notice '% % % % % %',bv,r,$3,$2,$1,data.room;

look like this= :

NOTICE: 1110110000111000000...0 13 4 BK1B =F7 4.58

The firs= t value is a binary string, 96 bits long, the tail are just zeroes, so I cu= t it.

The initial values in the table are:

design=3D# select = * from locations where coord=3D'BK1B' and store=3Dchr(247);
 store = | coord | room | mu | block |     tags   = ;   | details
-------+-------+------+----+-------+------------= ---+---------
 =F7     | BK1B  | 4.58 |&nb= sp; 1 | f     | 1010110000111 | D
(1 row)

And= now the error:

ProgrammingError: ERROR: new row for relation "locat= ions" violates check constraint
"location_room_check"
and then the context


If = I try to make the update manualy, works flawless (as 4.58-4 >=3D 0.0).
For now I droped the constrained, but how can I solve it?

Than= ks,
Sorin

--0-1277559103-1162508818=:23532--