[PGSQL/SP] Een zelfgedefineerde rij teruggeven

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Storeman storeman

storeman storeman

19/11/2008 11:31:00
Quote Anchor link
Het zit zo, ik heb een database met verzekeringen, deze verzekeringen hebben poliskosten, per verzekering kunnen deze varieren. Echter is er per maatschappij maar één keer een polis, dus ik wil de hoogste gebruiken. Dit is een mooie situatie voor een stored procedure, ik wil echter gelijk de maatschappij terugkrijgen, samen met de poliskosten.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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;


De vraag is welke types moet ik gebruiken?
 
PHP hulp

PHP hulp

12/08/2020 00:07:14
 
Storeman storeman

storeman storeman

20/11/2008 11:34:00
Quote Anchor link
Geen PGSQL expert in de zaal?
 

20/11/2008 14:00:00
Quote Anchor link
Wellicht dat pgFrank op vakantie is...
 
Storeman storeman

storeman storeman

20/11/2008 14:03:00
Quote Anchor link
Idd, pgFrank mag toch altijd graag het PG-woord verspreiden, en het sorteert bij mij iig effect :)
 
Barman V

Barman V

20/11/2008 14:14:00
Quote Anchor link
Werkt dit niet?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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;
?>


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
 
Storeman storeman

storeman storeman

20/11/2008 14:28:00
Quote Anchor link
@Barman,

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.
 
Barman V

Barman V

20/11/2008 14:46:00
Quote Anchor link
Dan moet je de return variabele als table instellen. Dat kan je hier vinden
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
 
Storeman storeman

storeman storeman

20/11/2008 14:53:00
Quote Anchor link
Kijk, dat is iets wat er op lijkt. Ik ga ermee aan de slag.

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
 
Barman V

Barman V

20/11/2008 14:57:00
Quote Anchor link
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 :)
 
Frank -

Frank -

21/11/2008 19:55:00
Quote Anchor link
Het verhaal is mij niet helemaal duidelijk, maar de structuren die hier worden voorgesteld, lijken mij niet helemaal lekker. De "oplossing" met een string en een ; als scheidingsteken, is helemaal een lapmiddel, dat had zo uit de jaren '70 van de vorige eeuw kunnen komen... ;)

Dit is een eenvoudig voorbeeldje van hoe ik het aanpak:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
-- 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';

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.
 
Storeman storeman

storeman storeman

24/11/2008 10:31:00
Quote Anchor link
@pgFrank, het klopt dat het niet noodzakelijk is dat ik maar één rij terugkrijg en dat verwacht ik ook niet.

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!
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
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;


Moet er alleen aan denken dat je m iets anders moet uitvoeren (wat natuurlijk logisch is, aangezien het benaderd wordt als tabel.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT * FROM booking.get_insurance_poliscosts( 2008100001 );
Gewijzigd op 01/01/1970 01:00:00 door storeman storeman
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.