Stored procedures met Postgres

[MATANDO]

Heelleu ppl,

Ik ben een beetje bezig met Postgres en wil daarin graag stored procedures maken. Echte stored procedures heb je niet heb ik al gemerkt maar een combinatie van een function en trigger/cursor moet voldoen.

Ik heb alleen ff een beginnetje nodig want ik kom er niet uit.

Ik wil:

code:

-- Function: getgenrename(varchar)
CREATE FUNCTION getgenrename(varchar) RETURNS varchar AS 'SELECT "genreName" FROM people 
JOIN genre AS g ("genreID", "genreName")
USING ("genreID")
WHERE "genreID" = $1'  LANGUAGE 'sql';

omzetten naar een SP die ipv alleen

code:

SELECT "genreName" FROM people

alles ophaalt zoals:

code:

SELECT * FROM people

Dat kan niet gewoon omdat een Function maar 1 result verwacht, hoe werk ik hier omheen?

Uiteindelijk wil ik met JSP de resultaten in een webpagina hangen, maar das voor een later stadium.

[tijn]

MATANDO schreef op 08 oktober 2002 @ 18:17:

omzetten naar een SP die ipv alleen

code:

SELECT "genreName" FROM people

alles ophaalt zoals:

code:

SELECT * FROM people

Dat kan niet gewoon omdat een Function maar 1 result verwacht, hoe werk ik hier omheen?

PostgreSQL kan enorm veel, maar dit kan nou net niet! Er zijn wel truuks te verzinnen waarmee je het een beetje kunt simuleren (cursor als resultaat van de functie), maar dat zijn niet echt fraaie oplossingen. Helaas...

[Verwijderd]

Dit is vast een heel dom antwoord, maar kan je geen array returnen en daar dan met subselects ofzo een beetje mee klooien?

[MATANDO]

Verwijderd schreef op 08 oktober 2002 @ 20:08:
Dit is vast een heel dom antwoord, maar kan je geen array returnen en daar dan met subselects ofzo een beetje mee klooien?

Volgens mij dus niet, als je namelijk een function maakt die meer resultaten teruggeeft dan wil hij hem niet aanmaken.

En ik weet dat er geen mooie oplossing bestaat, maar ik ben dus op zoek naar de lelijke oplossing ;-) Of misschien heeft iemand anders een andere suggestie voor me?

Ik wil het liefst in JSP developen, maar als PHP er een mooie oplossing heeft, dan kan ik overwegen om het daarmee te doen.

[xoror]

als het maar 1 kolom is kan je een set returnen. kan al vanaf 7.2 geloof ik.
zie de manual 12.2.4. http://www.postgresql.org/idocs/index.php?xfunc-sql.html

7.3 (in beta nu) kan ook wat complexere sets returnen.

fyi: je kan beter pl/pgsql oid usen om stored procs te schrijven. zijn wat flexibeler

[xoror]

tijn schreef op 08 oktober 2002 @ 19:28:
[...]

PostgreSQL kan enorm veel, maar dit kan nou net niet! Er zijn wel truuks te verzinnen waarmee je het een beetje kunt simuleren (cursor als resultaat van de functie), maar dat zijn niet echt fraaie oplossingen. Helaas...

waarom zou een cursor geen fraaie oplossing zijn ? het is vrij gangbaar hoor.

[MATANDO]

heb je toevallig een klein simpel voorbeeldje voor me?
een select * from bijv.

[xoror]

Lees de documentatie op de site. staan zat voorbeelden

je wil dus meerdere kolommen returnen. dat kan op 2 manieren.

1. Cursor returnen
2. Heap table vullen, die alleen geldig is voor die sesie


ps: waarom wil je in hemelsnaam een stored proc die toch alleen maar result terug geeft om etv af te beelden. Daar is een Select voor uitgevonden...

[MATANDO]

omdat sommige grotere selects veel opgevraagd zullen worden en dan werkt een stored procedure sneller

[xoror]

denk je dat dat echt zo veel uitmaakt ?

Hij voert gewoon zelfde query uit.het scheelt misschien iets omdat een en ander precompiled is. maar de daadwerkelijke query zal nog steeds veel tijd kosten. de 'opstart' kosten voor een 'normale' query zijn denk ik verwaarloos baar in dit geval.


voor grote sets hebben ze juist cursors uitgevonden. dan kan je zelf bepaalde rows ophalen die je nodig heb.

[MATANDO]

Cursors daar begin ik me net in te verdiepen.
Die lukte eerst niet omdat ik op Postgres 7.1.3 zat maar ik heb net 7.2 eropgezet dus ik zal daar zeker eens naar gaan kijken.

Toch vind ik het overzichtelijker om stored procedures te hebben in mijn JSP code straks + dat ze toch iets sneller opstarten.

[jochemd]

Het hangt een beetje af van het timeframe waarbinnen je je applicatie af wil hebben, maar je zou ook 7.3b2 kunnen gaan draaien. Als je je applicatie volgende week de deur uit moet hebben is dat geen goed idee, maar als je nog helemaal aan het begin zit en nog 3 maanden bezig bent is het risico volgens mij wel te overzien.

[xoror]

MATANDO schreef op 09 oktober 2002 @ 14:38:
Cursors daar begin ik me net in te verdiepen.
Die lukte eerst niet omdat ik op Postgres 7.1.3 zat maar ik heb net 7.2 eropgezet dus ik zal daar zeker eens naar gaan kijken.

Toch vind ik het overzichtelijker om stored procedures te hebben in mijn JSP code straks + dat ze toch iets sneller opstarten.

het maakt vrij weinig uit. tenzij je 7.3 gebruikt. die kan queryplannen cachen. dat scheelt al iets. maar omdat je het over zware query's heb, zijn die opstart kosten verwaarloosbaar tegen de executie tijd van je query.

[MATANDO]

Ik stap liever pas over als 7.3 gereleased is, mijn timeframe is ongeveer 2 maanden.

Ik test het nu met een cursor:

code:

CREATE FUNCTION GetMyShit() RETURNS refcursor AS'

BEGIN WORK;
    DECLARE liahona CURSOR
        
        FOR SELECT movie_name, genre_name, medium_name, movie_rating FROM movies as m
            INNER JOIN genre g
                ON g.genre_id = m.genre_id
            INNER JOIN medium md
                ON md.medium_id = m.medium_id;
        FETCH FORWARD 5 IN liahona;
        
    CLOSE liahona;
COMMIT WORK;

' LANGUAGE 'plpgsql'

Maar dat lukt niet echt, enig idee wat ik fout doe?

[ACM]

Als je SQL als taal neemt kan je vrij veel, aangezien je dan naast Curor's ook een "array" van records terug kan geven.

ik las het toevallig vorige week in de manual
even zoeken

http://www.postgresql.org/idocs/index.php?xfunc-sql.html
Daar staat iig uitgelegd hoe je een SETOF iets kan terug geven
Wellicht dat dat in PL/PGSQL ook kan.

[MATANDO]

de SETOF geeft meerdere rows terug, niet meerdere columns, en dat wil ik juist.

[xoror]

je bent beetje verward.

je moet de cursor wel open laten anders kan je buiten je stored proc niets mee doen. daarnaast is een stored proc altijd een transactie in pl/pgsql.

kijk hier voor example
http://archives.postgresq...eral/2002-05/msg00107.php

[ACM]

MATANDO schreef op 09 oktober 2002 @ 18:24:
de SETOF geeft meerdere rows terug, niet meerdere columns, en dat wil ik juist.

Je moet dan wel een setof record's teruggeven he?
Of gewoon een record, row of hoe die dingen ook heten.

Speur de documentatie van postgresql daar es verder af naar typen enzo die je kan gebruiken

[MATANDO]

Met een cursor kom ik wel verder, maar ik krijg geen cursor in een function omdat de cursor ook closed als de function closed.

Ik kan nergens vinden dat je een set of records kan terug geven met SETOF, alleen een set of rows, niet met meerdere columns.

[ACM]

MATANDO schreef op 10 oktober 2002 @ 17:16:
Met een cursor kom ik wel verder, maar ik krijg geen cursor in een function omdat de cursor ook closed als de function closed.

http://developer.postgres...gres/plpgsql-cursors.html
Lukt ze daar anders wel

Ik kan nergens vinden dat je een set of records kan terug geven met SETOF, alleen een set of rows, niet met meerdere columns.

Humm...
Je zou een SETOF [tabelnaam] terug kunnen geven

[ACM]

MATANDO schreef op 09 oktober 2002 @ 17:22:
Ik stap liever pas over als 7.3 gereleased is, mijn timeframe is ongeveer 2 maanden.

Ik test het nu met een cursor:

code:

CREATE FUNCTION GetMyShit() RETURNS refcursor AS'

BEGIN WORK;
    DECLARE liahona CURSOR
        
        FOR SELECT movie_name, genre_name, medium_name, movie_rating FROM movies as m
            INNER JOIN genre g
                ON g.genre_id = m.genre_id
            INNER JOIN medium md
                ON md.medium_id = m.medium_id;
        FETCH FORWARD 5 IN liahona;
        
    CLOSE liahona;
COMMIT WORK;

' LANGUAGE 'plpgsql'

Maar dat lukt niet echt, enig idee wat ik fout doe?

Waarom doe je hier trouwens een begin en commit

Dat heb je automatisch al doordat je in een functie body zit en voor selects heb je sowieso geen transacties nodig...
En als je een cursor returned, moet jehem niet sluiten natuurlijk maar returnen...

[MATANDO]

Bedankt voor jullie hulp, echter laat ik het hier even bij zitten omdat ik er simpelweg gewoon geen tijd meer voor heb.

Ik importeer nu gewoon de query zodat mijn asp documenten iig schoon blijven.