Datenbanken-Praktikum SS2012

create table Kunde
(
  Name varchar(100) not null,
  Geburtsdatum date,
  constraint Kunde_PK primary key (Name)
);


create table Wohnort
(
  ID serial not null,
  Postleitzahl char(5) not null,
  Ort varchar(100) not null,
  Strasse varchar(100) not null,
  Hausnummer varchar(5) not null,
  constraint Wohnort_PK primary key (id)
);
create unique index Wohnort_IX on Wohnort (Postleitzahl, Ort, Strasse, Hausnummer);


create table KundeWohnort
(
  Kundenname varchar(100) not null,
  WohnortID int not null,
  constraint KundeWohnort_PK primary key (Kundenname),
  constraint KundeWohnort_FK1 foreign key (Kundenname) references Kunde(Name) on delete cascade,
  constraint KundeWohnort_FK2 foreign key (WohnortID) references Wohnort(ID) on delete cascade
);

CREATE or replace FUNCTION KundeErzeugen(varchar, date, char, varchar, varchar, varchar)
RETURNS void
AS'
DECLARE
PName ALIAS FOR $1;
PGeburtsdatum ALIAS for $2;
PPostleitzahl ALIAS for $3;
POrt ALIAS for $4;
PStrasse ALIAS for $5;
PHausnummer ALIAS for $6;
zeile Wohnort%ROWTYPE;
BEGIN

SELECT * INTO zeile FROM Wohnort where Postleitzahl = PPostleitzahl and Ort = POrt and Strasse = PStrasse and Hausnummer = PHausnummer;
IF NOT FOUND THEN
  insert into Wohnort (Postleitzahl, Ort, Strasse, Hausnummer) values (PPostleitzahl, POrt, PStrasse, PHausnummer);
END IF;

--Kunde erzeugen:
insert into Kunde (Name, Geburtsdatum) values (PName, PGeburtsdatum);

--ID des Wohnorts holen. Der ist entweder vorhanden oder neu.
SELECT * INTO zeile FROM Wohnort where Postleitzahl = PPostleitzahl and Ort = POrt and Strasse = PStrasse and Hausnummer = PHausnummer;

insert into KundeWohnort (Kundenname, WohnortID) values (PName, zeile.ID);

END;
' LANGUAGE 'plpgsql';

select KundeErzeugen('Hans Mustermann', to_date('15.06.1960', 'DD.MM.YYYY'), '65321', 'Wiesbaden', 'Biebricher Allee', '12');
select KundeErzeugen('Gaby Mustermann', to_date('29.02.1990', 'DD.MM.YYYY'), '65321', 'Wiesbaden', 'Biebricher Allee', '12');

select * from wohnort;
select * from kunde;
select * from kundeWohnort;

drop FUNCTION kundeerzeugen(varchar, date, char, varchar, varchar, varchar);
drop table KundeWohnort;
drop table Kunde;
drop table Wohnort;

create table Kunde
(
  Name varchar(100) not null,
  Geburtsdatum date,
  constraint Kunde_PK primary key (Name)
);
--Wohnort aus letztem Beispiel ist hier nicht nötig.

create table DVD 
( 
  asin varchar(20) not null,
  constraint DVD_PK primary key (asin)
);
--Hier müsste eigentlich die Tabelle der Produktion kommen, die auf der DVD enthalten ist!

create table Exemplar 
( 
  nummer int not null,
  asin varchar(20) not null,
  constraint Exemplar_PK primary key (nummer, asin),
  constraint Exemplar_FK1 foreign key (asin) references DVD (asin) on delete cascade
);

create table Verleihvorgang
(
  asin varchar(20) not null,
  nummer int not null,
  kunde varchar(100) not null,
  constraint Verleihvorgang_PK primary key (asin, nummer, kunde),
  constraint Verleihvorgang_FK1 foreign key (asin, nummer) references Exemplar(asin, nummer) on delete restrict,
  constraint Verleihvorgang_FK2 foreign key (kunde) references KUNDE (name) on delete restrict
);

insert into DVD (asin) values ('0815');
insert into DVD (asin) values ('4711');

insert into Exemplar (asin, nummer) values ('0815', 1);
insert into Exemplar (asin, nummer) values ('0815', 2);
insert into Exemplar (asin, nummer) values ('4711', 1);
insert into Exemplar (asin, nummer) values ('4711', 2);


insert into Verleihvorgang (asin, nummer, kunde) values ('0815', 2, 'Hans Mustermann');

CREATE or replace FUNCTION KundeLoeschen(varchar)
RETURNS void
AS '
DECLARE
PName ALIAS FOR $1;
zeileVerleih Verleihvorgang%ROWTYPE;
zeileKunde Kunde%ROWTYPE;
BEGIN

--Gibt es den Kunden überhaupt?
select * INTO zeileKunde from Kunde where Name = PName;
IF NOT FOUND THEN
  Raise Exception ''Der Kunde % wurde nicht gefunden!'', PName;
END IF;

SELECT * INTO zeileVerleih FROM Verleihvorgang where Kunde = PName;
IF FOUND THEN
  Raise Exception ''Der Kunde % hat mindestens einen Verleihvorgang'', PName;
END IF;


--Kunde löschen
delete from Kunde where Name = PName;

END;
' LANGUAGE 'plpgsql';

select KundeLoeschen ('Hans Mustermann');
select KundeLoeschen ('Evelyn Nichtvorhanden');
select KundeLoeschen ('Gaby Mustermann');

drop FUNCTION KundeLoeschen(varchar);
drop table Verleihvorgang;
drop table Exemplar;
drop table DVD;
drop table Kunde;

CREATE or replace FUNCTION Alter(varchar)
RETURNS Int
AS '
DECLARE
PName ALIAS FOR $1;
zeileKunde Kunde%ROWTYPE;
BEGIN

--Gibt es den Kunden überhaupt?
select * INTO zeileKunde from Kunde where Name = PName;
IF NOT FOUND THEN
  Raise Exception ''Der Kunde % wurde nicht gefunden!'', PName;
END IF;


return Extract (year from Age(zeileKunde.Geburtsdatum));

END;
' LANGUAGE 'plpgsql';

select Alter ('Hans Mustermann');
select Alter ('Gaby Mustermann');
select Alter ('Evelyn Invalid');

drop FUNCTION Alter(varchar);

psql.exe --username postgres

psql.exe --dbname=knauftest --username=knauftest

C:\...\...>chcp
Aktive Codepage: 850.

C:\...\...>chcp 1252
Aktive Codepage: 1252.

datenbankname=> show client_encoding;
 client_encoding
-----------------
 UTF8
(1 Zeile)

datenbankname=> SET client_encoding TO 'win1252';
SET

select 'öäüÖÄÜß';

knauftest=> \l
                                         Liste der Datenbanken
   Name    | Eigentümer | Kodierung |    Sortierfolge     |     Zeichentyp
|  Zugriffsprivilegien
-----------+------------+-----------+---------------------+---------------------
+-----------------------
 knauftest | knauf      | UTF8      | German_Germany.1252 | German_Germany.1252
|
 postgres  | postgres   | UTF8      | German_Germany.1252 | German_Germany.1252
|
 template0 | postgres   | UTF8      | German_Germany.1252 | German_Germany.1252
| =c/postgres          +
           |            |           |                     |
| postgres=CTc/postgres
 template1 | postgres   | UTF8      | German_Germany.1252 | German_Germany.1252
| =c/postgres          +
           |            |           |                     |
| postgres=CTc/postgres
(4 Zeilen)