Meer doen met PostgreSQL [1]
Door Remco van Arkelen, 22 jaar geleden, 7.621x bekeken
Meer mogelijk maken door gebruik te maken van een aantal mogelijkheden die PostgreSQL te bieden heeft.
Gesponsorde koppelingen
Inhoudsopgave
- Inleiding
- Voorbeeld-case
- Overerving in PostgreSQL
- Aanmaken van tabellen en het leggen van relaties
- Views
- PL/pgSQL functies en procedures
- Triggers
- Check constraints en domains
- Conclusie
Er zijn 14 reacties op 'Meer doen met postgresql 1'
0 
-0 
-0
Deze maakt idd veel kans, ik heb volgens mij op google nog nooit zo'n tut kunnen vinden. Deze is echt heel mooi!
0 -0
-0
Thanx voor de reacties! De winnaar wordt willekeurig bepaald dus daar heeft de kwaliteit / kwantiteit van een tutorial (als het goed is) geen invloed op ;)
Deel 2 zit al een beetje te broeden in m'n hoofd, moet alleen nog ff de tijd hebben om het uit m'n vingers te schudden.
Deel 2 zit al een beetje te broeden in m'n hoofd, moet alleen nog ff de tijd hebben om het uit m'n vingers te schudden.
0 -0
-0
Ik heb hem nu net voor de 6e keer gelezen en het blijft een schitterende tutorial
Het wachten is op nummer 2
Het wachten is op nummer 2
0 -0
-0
Bedankt Klaasjan, leuk om te horen!...Het plan voor deel 2 is er nog steeds, 't ontbreekt me alleen een beetje aan tijd / inspiratie, dus als er dingen zijn die je graag uitgewerkt wilt zien hoor ik dat natuurlijk graag.
A.s. donderdag vertrek ik voor een paar daagjes naar Portugal en de grote dikke PgSQL-bijbel gaat natuurlijk gewoon mee :))
A.s. donderdag vertrek ik voor een paar daagjes naar Portugal en de grote dikke PgSQL-bijbel gaat natuurlijk gewoon mee :))
0 -0
-0
Kleine aanvulling: DOMAIN gebruik je per schema, je zult dus voor ieder schema opnieuw de benodigde datatypes moeten aanmaken.
Verder een prima tutorial die zit vraagt om broertjes en zusjes in dezelfde categorie... ;)
Verder een prima tutorial die zit vraagt om broertjes en zusjes in dezelfde categorie... ;)
0 -0
-0
Ben sinds kort eens met TRIGGERS gaan stoeien en vraag me nu af waarom je hier in de tutorial voor het voorbeeld van de controle hebt gekozen. Een controle klinkt mij in de oren als een 'check' en dan moet ik direct denken aan een check constraint.
Wat is nu het voordeel van een trigger t.o.v. een check constraint?
Zelf gebruik ik triggers om statussen van producten aan te passen na het ontvangen van de betaling. Een event (betaling ontvangen) triggert een proces: 'aanpassen status'.
Wat is nu het voordeel van een trigger t.o.v. een check constraint?
Zelf gebruik ik triggers om statussen van producten aan te passen na het ontvangen van de betaling. Een event (betaling ontvangen) triggert een proces: 'aanpassen status'.
0 -0
-0
Een CHECK CONSTRAINT was in dit eenvoudige voorbeeld inderdaad ook goed toepasbaar geweest, het was meer dat ik het onderwerp triggers even kort aan kon stippen, misschien door gebrek aan een beter voorbeeld of om de stof een beetje behapbaar te laten :)
Het voordeel van een trigger is dat je trigger-functions (en dus andere SQL procedures) kunt uitvoeren en daarmee veel meer functionaliteiten hebt. Een check constraint dwingt slechts af op correcte inhoud van een veld en zal alleen in werking treden als de check wordt overschreden, er wordt dan een foutmelding gegeven, je kunt hier (voor zover ik weet) geen andere acties aan hangen.
Zelf pas ik triggers steeds vaker toe, o.m. voor hetgeen jij noemt en de laatste tijd vooral voor archiveer-functies, voorraadbeheer, logging/journaling. Het is altijd even opletten om triggers niet in een oneindige lus te laten komen, waardoor ze elkaar blijven aanroepen, dit resulteert bijna gegarandeerd in een hangende PgSQL :)
Het voordeel van een trigger is dat je trigger-functions (en dus andere SQL procedures) kunt uitvoeren en daarmee veel meer functionaliteiten hebt. Een check constraint dwingt slechts af op correcte inhoud van een veld en zal alleen in werking treden als de check wordt overschreden, er wordt dan een foutmelding gegeven, je kunt hier (voor zover ik weet) geen andere acties aan hangen.
Zelf pas ik triggers steeds vaker toe, o.m. voor hetgeen jij noemt en de laatste tijd vooral voor archiveer-functies, voorraadbeheer, logging/journaling. Het is altijd even opletten om triggers niet in een oneindige lus te laten komen, waardoor ze elkaar blijven aanroepen, dit resulteert bijna gegarandeerd in een hangende PgSQL :)
0 -0
-0
Een check constraint kan een functie aanroepen, daar kun je natuurlijk van alles en nogwat in uitspoken. Of je dat ook wilt, dat is een tweede...
Goed dat je de oneindige lus even aanstipt, dat kan inderdaad een risico zijn. Met goede documentatie van het systeem moet dit grotendeels zijn af te vangen. Gelukkig kun je in pgSQL echt ?veral comments aan toevoegen! Nu alleen nog een tool die alle comments ook weer netjes op een rijtje zet en organiseert...
Goed dat je de oneindige lus even aanstipt, dat kan inderdaad een risico zijn. Met goede documentatie van het systeem moet dit grotendeels zijn af te vangen. Gelukkig kun je in pgSQL echt ?veral comments aan toevoegen! Nu alleen nog een tool die alle comments ook weer netjes op een rijtje zet en organiseert...
0 -0
-0
Hoi Frank,
N.a.v. je laatste reactie; ik zag niet helemaal hoe een check constraint een functie aan kan roepen, op http://www.postgresql.org/docs/current/static/ddl-constraints.html zag ik het niet direct terugkomen maar deze regel triggerde mij:
"It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression"
Je zou dus een functie kunnen maken welke een boolean teruggeeft en je in de check aanroept. Ik heb een heel klein testje gemaakt en het is op deze manier inderdaad mogelijk om functies aan te roepen in een check constraint:
Bovenstaand voorbeeld is ter illustratie, de controle op lengte zou ook direct in de check kunnen worden geplaatst:
Ik vraag me inderdaad ook wel af of je dit op deze manier zou willen, een check is een check, zaken zoals bijv. business-logica horen m.i. niet thuis in een check maar verplaats je naar functies, de checks gebruik ik zelf alleen als garanties voor juiste data in m'n tabellen.
N.a.v. je laatste reactie; ik zag niet helemaal hoe een check constraint een functie aan kan roepen, op http://www.postgresql.org/docs/current/static/ddl-constraints.html zag ik het niet direct terugkomen maar deze regel triggerde mij:
"It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression"
Je zou dus een functie kunnen maken welke een boolean teruggeeft en je in de check aanroept. Ik heb een heel klein testje gemaakt en het is op deze manier inderdaad mogelijk om functies aan te roepen in een check constraint:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE "phphulp"."users" (
"users_id" SERIAL,
"username" CHAR(50),
"password" CHAR(32),
CONSTRAINT "users_pkey" PRIMARY KEY("users_id"),
CONSTRAINT "users_chk_username" CHECK (phphulp.check_username_length(username))
) WITH OIDS;
CREATE OR REPLACE FUNCTION "phphulp"."check_username_length" (p_username char) RETURNS boolean AS
BEGIN
-- Hier kan allerlei SQL-code worden uitgevoerd
-- Controleer of de gebruikersnaam uit tenminste 5 karakters bestaat
RETURN LENGTH(p_username)>4;
END;
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
"users_id" SERIAL,
"username" CHAR(50),
"password" CHAR(32),
CONSTRAINT "users_pkey" PRIMARY KEY("users_id"),
CONSTRAINT "users_chk_username" CHECK (phphulp.check_username_length(username))
) WITH OIDS;
CREATE OR REPLACE FUNCTION "phphulp"."check_username_length" (p_username char) RETURNS boolean AS
BEGIN
-- Hier kan allerlei SQL-code worden uitgevoerd
-- Controleer of de gebruikersnaam uit tenminste 5 karakters bestaat
RETURN LENGTH(p_username)>4;
END;
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Bovenstaand voorbeeld is ter illustratie, de controle op lengte zou ook direct in de check kunnen worden geplaatst:
Code (php)
1
CONSTRAINT "users_chk_username" CHECK (LENGTH(username)>4)
Ik vraag me inderdaad ook wel af of je dit op deze manier zou willen, een check is een check, zaken zoals bijv. business-logica horen m.i. niet thuis in een check maar verplaats je naar functies, de checks gebruik ik zelf alleen als garanties voor juiste data in m'n tabellen.
0 -0
-0
Tja, business logica en de juiste plek om dit op te slaan... Een gevoelig en lastig onderwerp.
Neem een hotel met 50 kamers, waar ga je controleren of alle kamers zijn bezet? Het heeft geen enkele zin om nog een reservering op te slaan wanneer voor die datum alle kamers reeds zijn bezet. Dat zou je prima met een check-constraint kunnen doen, dat is waterdicht.
Maar dan komen er ineens 10 kamers bij en je past de controle aan (60 kamers => 60 gelijktijdige reserveringen). Dit verandert helemaal niks aan het soort data dat je opslaat (de reserveringen), hooguit het aantal gelijktijdige reserveringen dat je kunt hebben. Ik ben geneigd om de logica zo diep mogelijk in de database op te slaan, dan weet ik 100% zeker dat het goed gaat. Datums spelen altijd wel een belangrijke rol, wijzigingen van logica hebben altijd betrekking op toekomstige activiteiten. Je gaat geen reserveringen die reeds zijn verwerkt annuleren, reeds geplaatste reserveringen waar je geen ruimte meer voor hebt (slechts 40 kamers i.p.v. 50), zul je moeten annuleren. Dit kan implementatie van regels wel complex maken, je zult vaak ook data moeten converteren (of in dit geval zelfs moeten verwijderen/annuleren).
Met een sp in een check constraint is dit wel eenvoudig vooraf al vast te stellen, triggerfuncties kun je niet 'los' aanroepen.
Maar goed, zoals reeds gezegd, waar je de logica ook opslaat en toepast, alles heeft zo zijn voor- en nadelen.
Neem een hotel met 50 kamers, waar ga je controleren of alle kamers zijn bezet? Het heeft geen enkele zin om nog een reservering op te slaan wanneer voor die datum alle kamers reeds zijn bezet. Dat zou je prima met een check-constraint kunnen doen, dat is waterdicht.
Maar dan komen er ineens 10 kamers bij en je past de controle aan (60 kamers => 60 gelijktijdige reserveringen). Dit verandert helemaal niks aan het soort data dat je opslaat (de reserveringen), hooguit het aantal gelijktijdige reserveringen dat je kunt hebben. Ik ben geneigd om de logica zo diep mogelijk in de database op te slaan, dan weet ik 100% zeker dat het goed gaat. Datums spelen altijd wel een belangrijke rol, wijzigingen van logica hebben altijd betrekking op toekomstige activiteiten. Je gaat geen reserveringen die reeds zijn verwerkt annuleren, reeds geplaatste reserveringen waar je geen ruimte meer voor hebt (slechts 40 kamers i.p.v. 50), zul je moeten annuleren. Dit kan implementatie van regels wel complex maken, je zult vaak ook data moeten converteren (of in dit geval zelfs moeten verwijderen/annuleren).
Met een sp in een check constraint is dit wel eenvoudig vooraf al vast te stellen, triggerfuncties kun je niet 'los' aanroepen.
Maar goed, zoals reeds gezegd, waar je de logica ook opslaat en toepast, alles heeft zo zijn voor- en nadelen.
Om te reageren heb je een account nodig en je moet ingelogd zijn.
Inhoudsopgave
- Inleiding
- Voorbeeld-case
- Overerving in PostgreSQL
- Aanmaken van tabellen en het leggen van relaties
- Views
- PL/pgSQL functies en procedures
- Triggers
- Check constraints en domains
- Conclusie
PHP hulp
0 seconden vanaf nu