Thread: nested if , and how to trigger....

Hi,
can we do nested if in function ? would you give me some example?

second, if i've create table like this:

create table tb (
code varchar(4),
classes varchar(6),
lecture varchar(4),
th int2,
day varchar(1),
hr varchar(1),
room varchar(4),
primary key (code,kl,ds));

my questions is how to prevent this record to be insert:
code     classes  lecture      th         day   hr     room
K021    1tip01   1011        5          1      1      1111
K022    1tip01   1011        5          1      1      1111

or :
code     classes  lecture      th         day   hr     room
K021    1tip01   1011        5          1      1      1111
K021    1tip01   1012        5          1      1      1111

TIA
Didi

On Sun, 6 May 2001, Muhammad Rusydi wrote:

> Hi,
> can we do nested if in function ? would you give me some example?

In what context?  In plpgsql, you can nest if's pretty normally:
create function f() returns int4 as '
begin
 if (1=1) then
  if (1=0) then
   raise exception ''1=0'';
  else
   raise notice ''1!=0'';
  end if;
 else
  raise exception ''1!=1'';
 end if;
 return 0;
end;' language 'plpgsql';


> second, if i've create table like this:
>
> create table tb (
> code varchar(4),
> classes varchar(6),
> lecture varchar(4),
> th int2,
> day varchar(1),
> hr varchar(1),
> room varchar(4),
> primary key (code,kl,ds));

code,kl,ds?  What are kl and
ds?

> my questions is how to prevent this record to be insert:
> code     classes  lecture      th         day   hr     room
> K021    1tip01   1011        5          1      1      1111
> K022    1tip01   1011        5          1      1      1111
>
> or :
> code     classes  lecture      th         day   hr     room
> K021    1tip01   1011        5          1      1      1111
> K021    1tip01   1012        5          1      1      1111

If code or lecture must always be unique, then use a unique
constraint.  If you can have duplicate codes if and only
if the lecture is the same and you can have duplicate lectures
if and only  if the code is the same, you'll probably need
to do a before insert/update trigger that checks for you
(and you'll need to lock the table i think so that you
can't have two transactions inserting inconsistant data
that the other can't see due to it not being committed)

Hi,

> > create table tb (
> > code varchar(4),
> > classes varchar(6),
> > lecture varchar(4),
> > th int2,
> > day varchar(1),
> > hr varchar(1),
> > room varchar(4),
> > primary key (code,kl,ds));
>
> code,kl,ds?  What are kl and
> ds?
sorry, it should be code,classes,and lecture

>
> > my questions is how to prevent this record to be insert:
> > code     classes  lecture      th         day   hr     room
> > K021    1tip01   1011        5          1      1      1111
> > K022    1tip01   1011        5          1      1      1111
> >
> > or :
> > code     classes  lecture      th         day   hr     room
> > K021    1tip01   1011        5          1      1      1111
> > K021    1tip01   1012        5          1      1      1111
>

code is for the subject that lecture teach on the class
what i really want to do is how to prevent:
1. different subject will not be give at same time (day and hr), same room
2. different lectures with same subject will not be at same time and room
too
3. but one lecture can teach same subject in different classes at same time
and room
TIA
Didi

I think the question was whether a function can be called from within a
function.  I sure hope so, although I don't know...

Ian

Stephan Szabo wrote:

> On Sun, 6 May 2001, Muhammad Rusydi wrote:
>
> > Hi,
> > can we do nested if in function ? would you give me some example?
>
> In what context?  In plpgsql, you can nest if's pretty normally:
> create function f() returns int4 as '
> begin
>  if (1=1) then
>   if (1=0) then
>    raise exception ''1=0'';
>   else
>    raise notice ''1!=0'';
>   end if;
>  else
>   raise exception ''1!=1'';
>  end if;
>  return 0;
> end;' language 'plpgsql';
>
> > second, if i've create table like this:
> >
> > create table tb (
> > code varchar(4),
> > classes varchar(6),
> > lecture varchar(4),
> > th int2,
> > day varchar(1),
> > hr varchar(1),
> > room varchar(4),
> > primary key (code,kl,ds));
>
> code,kl,ds?  What are kl and
> ds?
>
> > my questions is how to prevent this record to be insert:
> > code     classes  lecture      th         day   hr     room
> > K021    1tip01   1011        5          1      1      1111
> > K022    1tip01   1011        5          1      1      1111
> >
> > or :
> > code     classes  lecture      th         day   hr     room
> > K021    1tip01   1011        5          1      1      1111
> > K021    1tip01   1012        5          1      1      1111
>
> If code or lecture must always be unique, then use a unique
> constraint.  If you can have duplicate codes if and only
> if the lecture is the same and you can have duplicate lectures
> if and only  if the code is the same, you'll probably need
> to do a before insert/update trigger that checks for you
> (and you'll need to lock the table i think so that you
> can't have two transactions inserting inconsistant data
> that the other can't see due to it not being committed)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly