[PGSQL/SP] Een zelfgedefineerde rij teruggeven
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE FUNCTION booking.get_insurance_poliscosts(bigint)
RETURNS ???? AS
$BODY$
DECLARE
p_rows ???;
p_bookingid ALIAS FOR $1;
BEGIN
p_rows := DISTINCT MAX(insurances.polis_costs) AS polis_costs, companies.name AS companyname
FROM booking.insurances
LEFT JOIN insurance.companies ON companies.id = insurances.company_id
WHERE insurances.booking_id = p_bookingid
GROUP BY insurances.company_id, companies.name;
RETURN p_rows;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
RETURNS ???? AS
$BODY$
DECLARE
p_rows ???;
p_bookingid ALIAS FOR $1;
BEGIN
p_rows := DISTINCT MAX(insurances.polis_costs) AS polis_costs, companies.name AS companyname
FROM booking.insurances
LEFT JOIN insurance.companies ON companies.id = insurances.company_id
WHERE insurances.booking_id = p_bookingid
GROUP BY insurances.company_id, companies.name;
RETURN p_rows;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
De vraag is welke types moet ik gebruiken?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
CREATE OR REPLACE FUNCTION booking.get_insurance_poliscosts(bigint)
RETURNS varchar AS
$BODY$
DECLARE
p_rows varchar(1000);
p_bookingid ALIAS FOR $1;
BEGIN
p_rows := DISTINCT MAX(insurances.polis_costs) + ';' + companies.name AS polisinfo
FROM booking.insurances
LEFT JOIN insurance.companies ON companies.id = insurances.company_id
WHERE insurances.booking_id = p_bookingid
GROUP BY insurances.company_id, companies.name;
RETURN p_rows;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
?>
CREATE OR REPLACE FUNCTION booking.get_insurance_poliscosts(bigint)
RETURNS varchar AS
$BODY$
DECLARE
p_rows varchar(1000);
p_bookingid ALIAS FOR $1;
BEGIN
p_rows := DISTINCT MAX(insurances.polis_costs) + ';' + companies.name AS polisinfo
FROM booking.insurances
LEFT JOIN insurance.companies ON companies.id = insurances.company_id
WHERE insurances.booking_id = p_bookingid
GROUP BY insurances.company_id, companies.name;
RETURN p_rows;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
?>
Nu krijg je het terug als ; seperated (varchar).
Bijv: 134.50;bedrijfsnaam
Vervolgens kan je in PGSQL de variabelen uit elkaar trekken.
EDIT
Of zie: http://p2p.wrox.com/topic.asp?TOPIC_ID=45369
Gewijzigd op 01/01/1970 01:00:00 door Barman V
uiteraard is dat een leuke variant, zo zou je ook een stringarray terug kunnen geven, maar juist een functie die custom rijen terug kan geven kan erg krachtig zijn en ik ben daar gewoon erg benieuwd naar.
Voor nood zou ik de door jou aangedragen oplossing kunnen gaan gebruiken, maar voor de mooi doe ik het liever op een andere manier.
http://p2p.wrox.com/topic.asp?TOPIC_ID=45369
Of Google:
http://www.google.nl/search?sourceid=navclient&hl=nl&ie=UTF-8&rlz=1T4GGIH_nlNL282NL285&q=pgsql+function+return+table
Zo krijg je dus een nieuwe tijdelijke tabel terug waar je de resultaten uit kunt halen. Dit is in PLSQL denk ik de krachtigste manier om dit te doen.
EDIT:
Het is alweer even geleden dat ik iets met PLSQL gedaan heb, maar volgens mij kan je ook gebruik maken van XML.
In SQL Server heb je in ieder geval de optie FOR XML EXPLICIT. Deze maakt direct XML van het resultaat uit een select query. In Oracle zou je ook wel handige manieren hebben om xml te maken en uit te lezen.
Die vervelende gestoorde procedures ook :P
Gewijzigd op 01/01/1970 01:00:00 door Barman V
Die stored procedures zijn inderdaad vervelend. Om een complete boeking inclusief totalen uit een schema te trekken zou met php toch enkele tienden van seconden duren per boeking. SP's icm views laten me simpelweg weer queries draaien op resultaten van SP's.
Ik zeg: Hulde :).
Edit:
Ik had het sarcase er uiteraard wel uit gehaald
Gewijzigd op 01/01/1970 01:00:00 door storeman storeman
storeman schreef op 20.11.2008 14:53:
Die stored procedures zijn inderdaad vervelend. Om een complete boeking inclusief totalen uit een schema te trekken zou met php toch enkele tienden van seconden duren per boeking. SP's icm views laten me simpelweg weer queries draaien op resultaten van SP's.
Haha het was niet serieus bedoeld :)
Maar een collega van mij heeft het altijd over 'gestoorde procedures'. Hierdoor kon ik 'stored procedures' even een tijdje niet meer normaal uitspreken :)
Dit is een eenvoudig voorbeeldje van hoe ik het aanpak:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Function: get_address(integer, text, integer, text)
-- DROP FUNCTION get_address(integer, text, integer, text);
CREATE OR REPLACE FUNCTION test(IN nummer integer, OUT kosten text, OUT naam text)
RETURNS SETOF record AS
$BODY$
DECLARE
query TEXT;
row RECORD;
BEGIN
query := '
SELECT
kosten,
naam
FROM
tabelnaam
WHERE
' || nummer || ' BETWEEN x AND y';
FOR row IN EXECUTE query LOOP
kosten := row.kosten;
naam := row.naam;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';
-- DROP FUNCTION get_address(integer, text, integer, text);
CREATE OR REPLACE FUNCTION test(IN nummer integer, OUT kosten text, OUT naam text)
RETURNS SETOF record AS
$BODY$
DECLARE
query TEXT;
row RECORD;
BEGIN
query := '
SELECT
kosten,
naam
FROM
tabelnaam
WHERE
' || nummer || ' BETWEEN x AND y';
FOR row IN EXECUTE query LOOP
kosten := row.kosten;
naam := row.naam;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';
1) Geef de IN - parameters op met de juiste datatypes
2) Geef de OUT - resultaten op met de juiste datatypes
3) SETOF record, je hebt heel vaak met records te maken, dat is hier ook het geval
4) Zet de uit te voeren query in een string, dan kun je hem eenvoudig opstellen, testen en opvragen wanneer het fout gaat: RAISE NOTICE
5) FOR LOOP om door de resultaten heen te wandelen. Vanaf versie 8.3 is het niet meer nodig om de query resultaten in variabelen te stoppen, je kunt dan direct RETURN NEXT; gebruiken. Het voorbeeld gebruikt de oude notatie die je al sinds jaar en dag werkt.
Foutafhandeling ontbreekt nog, die mag je zelf in de SP zetten. Ik vind het overigens raar dat je stelt dat de query maar 1 resultaat zal opleveren, daar is vanuit de query gezien geen enkele reden voor. Wanneer dat een eis is, zet dan wel een LIMIT 1 in je query of gooi een EXCEPTION wanneer je meerdere resultaten krijgt. Dat is eigenlijk nog de beste oplossing, er treedt tenslotte een uitzondering op.
Ik wil er niet al te diep op ingaan, maar ik zal kort toelichten waar ik heen wil:
1. Tabel met verzekeringsmaatschappijen (bevat het veld poliskosten)
2. Tabel met verzekeringen, gekoppeld aan die maatschappijen
3. Tabel met afgesloten verzekeringen (gekoppeld aan verzekeringen en een boekingstabel). De tarief gegevens worden hierin gekopieerd, zodat deze niet meeveranderen als de gegevens van de verzekering veranderen.
Het kan nu dus voorkomen dat er meerdere verzekeringen bij dezelfde maatschappij zijn afgesloten met verschillende poliskosten, deze moeten per maatschappij maar één keer gerekend worden.
Ik wil dus niet dat mijn sp een record teruggeeft, maar meer zoiets als een tabel uit de post van Barman.
Edit:
Ik ben nog even bezig geweest met de info van pgFrank, en t lijkt erop!
Moet er alleen aan denken dat je m iets anders moet uitvoeren (wat natuurlijk logisch is, aangezien het benaderd wordt als tabel.
Ik ben nog even bezig geweest met de info van pgFrank, en t lijkt erop!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE FUNCTION booking.get_insurance_poliscosts(IN bookingid bigint, OUT polis_costs double precision, OUT company character varying(255) )
RETURNS SETOF record AS
$BODY$
DECLARE
p_row RECORD;
query TEXT;
BEGIN
query := 'SELECT DISTINCT MAX(insurances.polis_costs) AS polis_costs, companies.name AS companyname
FROM booking.insurances
LEFT JOIN insurance.insurances AS orgins ON orgins.id = insurances.insurance_id
LEFT JOIN insurance.companies ON companies.id = orgins.company_id
WHERE insurances.booking_id = ' || bookingid || '
GROUP BY companies.name';
FOR p_row IN EXECUTE query LOOP
polis_costs := p_row.polis_costs;
company := p_row.companyname;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
RETURNS SETOF record AS
$BODY$
DECLARE
p_row RECORD;
query TEXT;
BEGIN
query := 'SELECT DISTINCT MAX(insurances.polis_costs) AS polis_costs, companies.name AS companyname
FROM booking.insurances
LEFT JOIN insurance.insurances AS orgins ON orgins.id = insurances.insurance_id
LEFT JOIN insurance.companies ON companies.id = orgins.company_id
WHERE insurances.booking_id = ' || bookingid || '
GROUP BY companies.name';
FOR p_row IN EXECUTE query LOOP
polis_costs := p_row.polis_costs;
company := p_row.companyname;
RETURN NEXT;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Moet er alleen aan denken dat je m iets anders moet uitvoeren (wat natuurlijk logisch is, aangezien het benaderd wordt als tabel.
Code (php)
1
SELECT * FROM booking.get_insurance_poliscosts( 2008100001 );
Gewijzigd op 01/01/1970 01:00:00 door storeman storeman