[postgresql] zichzelf aanroepende functie en een cursor

[JaQ]

Ik wordt een beetje gek van het plpgsql momenteel. Volgens mij ben ik tenminste tegen een beperking aangelopen. Helaas ligt de postgresql website weer eens plat, dus in de archieven daar zoeken is geen optie. De manual leverd voor alsnog ook geen uitsluitsel (ja, die heb ik off-line, ivm de slechte bereikbaarheid van www.postgresql.org)

Goed, ik heb dus een functie gemaakt die zichzelf aanroept. Dit is de code:

code:

create or replace function get_user_id (users.username%type)
  returns users.id%type
  as '

declare

  pv_username alias for $1;
  lv_user_id  users.id%type;

  c_user cursor for select id from users where username = pv_username
                    union
                    select get_user_id (get_default_value(''username''));

begin

  open c_user;
  fetch c_user into lv_user_id;
  close c_user;
  return lv_user_id;

end;

  ' language plpgsql;

Niet zo spannend volgens mij. Het id van een user wordt opgehaald a.h.v een username, als die niet bestaat wordt de default waarde opgehaald. Als ik deze functie aanroep krijg ik echter de volgende error:

ERROR: cursor "c_user" already in use

Een recursieve functie met een cursor is dus niet mogelijk? erg vreemd volgens mij, maar goed. Ik zit er mooi wel mee, want ik weet zo 123 geen workaround (behalve dan de cursor eruit te gooien en voor de select into optie te gaan).

Is er al eens iemand anders tegen dit probleem aangelopen en zo ja, wat voor oplossing heb je toe gekozen om hier omheen te werken? Het gaat overigens om postgresql 7.3.3

[ACM]

Is het niet handiger om gewoon een functie get_default_user_id te maken en de default value van je username ook juist te laten afhangen van die default_user_id (aangezien dat tenslotte de identificerende waarde is)?

[JaQ]

ACM schreef op 28 July 2003 @ 12:03:
Is het niet handiger om gewoon een functie get_default_user_id te maken en de default value van je username ook juist te laten afhangen van die default_user_id (aangezien dat tenslotte de identificerende waarde is)?

Die snap ik niet helemaal.

Ik heb nu een grote tabel met default_values. (default_value, value) Daar kan ik dus ongelimiteerd defaults indrukken. Deze lees ik dus uit met de functie get_default_value De default_value die ik als parameter meegeef is dus eigenlijk de pk van die tabel. Denk dus b.v. aan default username, maar ook background color (voor applicatie) etc. beetje lelijk, maar wel functioneel en zeker ook generieker dan voor iedere default value een nieuwe functie aan te gaan maken.

De get_user_id functie haalt dus een id bij een username op. Als de opgegeven username nou niet bestaat om een of andere reden, dan moet ie automagisch de default pakken (en logt ie netjes naar een mysql database dat er iemand loopt te klooien). Deze constructie wil ik in principe in bijna alle functies gebruiken. (ik leg dus zoveel mogelijk business rules in de database vast, zodat de php-klopper die hier straks een app op gaat maken in ieder geval de integriteit van de database niet kan vern**ken. Hij kan enkel roepen: select new_user(parameter, parameter); en dan handeld plpgsql de rest af.

Eigenlijk gaat het me ook meer om het principe. Het is dus gewoon niet mogelijk om binnen 1 transactie 2 cursoren te openen met dezelfde naam (ook al zit het in een ander blok en zou het een andere namespace moeten zijn). Is dit een feature of is dit een bug?

(p.s. ik weet ook wel dat ik eigenlijk een hele foute query gebruik, een constructie met in i.p.v. union is hier veel beter op z'n plaats, maar het gaat om het proof-of-concept nu)

[ACM]

Ah, dan is het nog simpeler

Sla de default userid op in die tabel en vraag die op met je get_default_value

De userid is tenslotte wat je wilt weten, niet een username en als je dan perse af en toe een speciale default wilt teruggeven, kan je net zo goed die default opvragen.

Je vraag of het een feature of bug is weet ik echt geen antwoord op, probeer (zodra het weer up is) de mailinglists van postgres es, in dit geval wil je denk ik postgresql-general, gewoon registreren en je vraag vriendelijk verpakt stellen, krijg je bijna gegarandeerd een goed antwoord

[JaQ]

ACM schreef op 28 July 2003 @ 12:18:
Ah, dan is het nog simpeler

Sla de default userid op in die tabel en vraag die op met je get_default_value

Dat is een optie (maar eigenlijk natuurlijk een beetje dubbel )

ACM schreef op 28 July 2003 @ 12:18:
Je vraag of het een feature of bug is weet ik echt geen antwoord op, probeer (zodra het weer up is) de mailinglists van postgres es, in dit geval wil je denk ik postgresql-general, gewoon registreren en je vraag vriendelijk verpakt stellen, krijg je bijna gegarandeerd een goed antwoord

Dat zal ik maar eens moeten gaan doen dan. Baal er goed van dat ik langzamerhand tegen beperkingen op begin te lopen. Volgens mij ben ik echt geen super-guru op plsql gebied, maar ik begin toch wel wat last te krijgen van het verschil oracle <-> postgresql.

Toch bedankt voor het helpen met nadenken.

[ACM]

DrFrankenstoner schreef op 28 July 2003 @ 12:24:
Dat is een optie (maar eigenlijk natuurlijk een beetje dubbel )

Je kan de username eruit gooien dan, die kan je uniek en eenvoudig achterhalen met die userid, is het niet dubbel meer

Dat zal ik maar eens moeten gaan doen dan. Baal er goed van dat ik langzamerhand tegen beperkingen op begin te lopen. Volgens mij ben ik echt geen super-guru op plsql gebied, maar ik begin toch wel wat last te krijgen van het verschil oracle <-> postgresql.

Sja, uiteraard heeft postgresql beperkingen. Daar zul je enerzijds mee om moeten leren gaan en anderzijds omheen moeten leren werken. Wellicht is het zelfs een nadeel dat je Oracle-ervaring hebt, aangezien je daardoor in Oracle-termen gaat denken en die dan in Postgres op gaat zoeken

[jochemd]

DrFrankenstoner schreef op 28 July 2003 @ 11:26:

code:

create or replace function get_user_id (users.username%type)
  returns users.id%type
  as '

declare

  pv_username alias for $1;
  lv_user_id  users.id%type;

  c_user cursor for select id from users where username = pv_username
                    union
                    select get_user_id (get_default_value(''username''));

begin

  open c_user;
  fetch c_user into lv_user_id;
  close c_user;
  return lv_user_id;

end;

  ' language plpgsql;

Zou je niet een parameterized cursor nemen?

[JaQ]

jochemd schreef op 28 July 2003 @ 12:36:
[...]
Zou je niet een parameterized cursor nemen?

Je bedoeld dat ik de username als parameter aan de cursor voer? Op zich maakt dat voor het opnieuw openen van de cursor toch niet uit? Dan open ik nog steeds de cursor 2 keer.

code:

c_user cursor 
for 
select id 
from users 
where username in (pv_username
                  ,get_user_id (get_default_value(''username'')
                   );

werkt ook (en is zelfs een betere query), maar het gaat me niet zozeer om de query, veel meer om het principe dat ik een functie niet recursief kan aanroepen als ik een cursor gebruik in deze functie.

[jochemd]

DrFrankenstoner schreef op 28 July 2003 @ 12:50:

Je bedoeld dat ik de username als parameter aan de cursor voer? Op zich maakt dat voor het opnieuw openen van de cursor toch niet uit? Dan open ik nog steeds de cursor 2 keer.

Volgens mij is de reden waarom je dit probleem hebt het moment waarop het query plan gecompileerd wordt. Als ik het goed heb verschuift dat met het type cursor (zeker als je een unbound cursor neemt). Tenzij ik in de war ben met het compileren van TCL