From postgresql-sql Thu Nov 02 23:06:58 2006 From: Sorin Schwimmer Date: Thu, 02 Nov 2006 23:06:58 +0000 To: postgresql-sql Subject: [SQL] PLPGSQL question Message-Id: <20061102230658.25239.qmail () web56002 ! mail ! re3 ! yahoo ! com> X-MARC-Message: https://marc.info/?l=postgresql-sql&m=116283980921407 MIME-Version: 1 Content-Type: multipart/mixed; boundary="--0-1277559103-1162508818=:23532" --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--