Message-ID: <412DE07D.1060800@free.fr>
Date: Thu, 26 Aug 2004 15:07:09 +0200
From: Patrice OLIVER <oliverp21@free.fr>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Problem with stored procedure
Content-Type: multipart/mixed; boundary="------------020000040409060302000000"

This is a multi-part message in MIME format.
--------------020000040409060302000000
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

In this example, I use 2 tables :
create table types (
typnum integer primary key,
catcode varchar(2),
typlib varchar(35));

create table uv (
uvnum varchar(5) primary key,
typnum integer,
uvlib varchar(50));

alter table uv
   add constraint fk_uv_type foreign key (typnum)
      references types (typnum)
      on delete restrict on update restrict;

I also use stored procedures :

create function numtype(varchar) returns integer as '
declare
   codetype alias for $1;
   coderet integer;
begin
   select into coderet typnum from types
       where typcode = codetype and catcode = ''UV'';
   return coderet;
end;
' language 'plpgsql';

create function insert_uv(varchar,varchar,varchar) returns integer as '
declare
   codeuv alias for $1;
   codetype alias for $2;
   libuv alias for $3;
   cletype integer;
begin
   select into cletype numtype(codeuv);
   insert into uv values (codeuv, cletype, libuv);
   return cletype;
end;
' language 'plpgsql';

When I do :

select insert_uv('SGBD','DUVC','TEST BD');
I get the following message :
ERROR:  insert or update on table "uv" violates foreign key constraint 
"fk_uv_caracteri_type"
DETAIL:  Key (typnum)=(43) is not present in table "types".

I don't kown why.

Anyone has an idea ?

With best regards,
Patrice

--------------020000040409060302000000
Content-Type: text/x-vcard; charset=utf-8;
 name="oliverp21.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="oliverp21.vcf"

begin:vcard
fn:Patrice OLIVER
n:OLIVER;Patrice
email;internet:oliverp21@free.fr
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------020000040409060302000000--