ugrás a tartalomhoz

php - pgsql

inf · 2013. Júl. 3. (Sze), 16.38
Valaki használta már a php-t postgresql-el?

Van több gondom is vele, a tárolt eljárásoknál a pg_ execute elég fura resultset-et ad. A tárolt eljárás neve van benne, a tényleges eredményt meg megpróbálja string-ként összefűzni... Ha kell küldök kódot is, nem tudom hogy javítható. Egyelőre addig se jutottam el, hogy tárolt eljárással kiolvassam egy tábla tartalmát, aztán azt átadjam php-nek. Mysql-ben ez kb annyi lenne, hogy

CREATE PROCEDURE `user_read_all`(
)
BEGIN
    SELECT `user_id` AS `id`, `user_name` AS `name`, `user_email` AS `email` FROM `user`
    ORDER BY `user_id` DESC;
END;


$stmt = $pdo->query('CALL `user_read_all`()');
$stmt->fetchAll();
Írtam pg függvényekre valami hasonlót, mint pdo. Tudom, hogy van pdo lib pgsql-hez is, de a szolgáltatónál egyelőre csak sima pg függvények vannak fent. Majd megkérdem mennyiért telepítenék. Nem tudom, hogy pdo-val is ugyanúgy rossz lenne e az eredmény, majd később kipróbálom...

Azt hittem a plpgsql-el könnyebb dolgom lesz, mint a mysql-el, de nem igazán találtam semmi használható tutorialt eddig. Legalábbis pont erre a feladatra nem, ellenben minden másra igen. :-) Végülis ugyanez megoldható sima pgsql-es tárolt eljárással is, de jobban tetszene, ha menne plpgsql-ből, mert később bármikor szükség lehet rá.

CREATE OR REPLACE FUNCTION user_read_all()
  RETURNS table(user_id INT, user_name VARCHAR, user_email VARCHAR)
AS
  $BODY$
  BEGIN
    return query SELECT
      user_id, user_name, user_email
    FROM
      user;
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Egyelőre eddig jutottam, de teljesen rossz.

array(1) { [0]=> array(1) { ["test"]=> string(4) "(,,)" } }
Ezt kapom vissza.

Ha a returns-ben átírom az id-ket, és beteszek mondjuk az user_id-hez egy as id-t, akkor meg syntax error-t... Nem túl baráti... Az automatikus kiegészítés sem segít valami sokat, pl a TABLE és RECORD típusokat egyáltalán nem is ismeri, pedig ezek közül kéne valamelyiket használni. Mondjuk a legjobb az lenne, ha automatikusan állítaná be a típust, nem nekem kéne kézzel megadni. A táblák alapján elvileg tudnia kéne...
‹ File_get_content Vs Fread a műveletnek megfelelő header-ek küldése válaszként ›
 ■
1

select * from list_users()

BlaZe · 2013. Júl. 3. (Sze), 21.32
create or replace function list_users() returns 
  table (user_id bigint,name varchar,login varchar) as 
$body$
begin
  return query select u.user_id,md5(u.name)::varchar,md5(u.login)::varchar 
  from users as u limit 1;
end;
$body$
language plpgsql;

select list_users();
                              list_users                               
-----------------------------------------------------------------------
 (2,1ef45349d8158e9ba5a8715906023ddc,c2fe677a63ffd5b7ffd8facbf327dad0)
Vs
select * from list_users();

 user_id |               name               |              login               
---------+----------------------------------+----------------------------------
       2 | 1ef45349d8158e9ba5a8715906023ddc | c2fe677a63ffd5b7ffd8facbf327dad0

<?php
$connStr = "...";
$conn = pg_connect($connStr);
$res = pg_query($conn,'select * from list_users();');
$arr = pg_fetch_assoc($res);
var_dump($arr);
pg_close($conn);
?>
Result:
array (size=3)
  'user_id' => string '2' (length=1)
  'name' => string '1ef45349d8158e9ba5a8715906023ddc' (length=32)
  'login' => string 'c2fe677a63ffd5b7ffd8facbf327dad0' (length=32)
Szóval szerintem rossz a queryd :) Ha table a result, akkor úgy is kell belőle selectelni, különben jogosan 1 fieldbe konkatenálja össze a fieldeket.
3

Na kipróbálom így, select

inf · 2013. Júl. 4. (Cs), 00.36
Na kipróbálom így, select user_read_all()-t használok, azt olvastam így kell, de ezekszerint ha resultsetet ad vissza, akkor úgy, ahogy te írod. Remélem ez a megközelítés működik olyanoknál is, aminél nem várok eredményt, vagy csak egyetlen értéket várok... Majd azt is kipróbálom. Jelenleg írtam egy proxy-t, ami meghívja a tárolt eljárásokat __call-al, az átadott paramétereket meg szövegként átadja. Egyelőre ez a megközelítés elég jól bevált, alternatívája lehet az orm-es megoldásoknak. A mysql szintén alkalmas ilyesmire, viszont a postgresql fejlettebb, azért most azzal próbálkozom...
5

CREATE FUNCTION test ()

inf · 2013. Júl. 4. (Cs), 01.01

CREATE FUNCTION test ()
  RETURNS table(id INT, name VARCHAR, email VARCHAR)
AS
  $BODY$
  BEGIN
    return query SELECT
      "user".user_id as id, "user".user_name as name, "user".user_email as email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Ezzel mi a baj?
Közben rájöttem, hogy kulcsfontosságú úgy lekérni a mezők adatait, hogy elé tegyem a tábla nevét, különben egyszerűen csak null-t tesz be értéknek, és közben még hibaüzenetet sem kapok.

A fenti kód syntax error-t ír, sima sql-ben viszont valid lenne... :S
6

A query és a SELECT közé nem

H.Z. · 2013. Júl. 4. (Cs), 08.43
A query és a SELECT közé nem kellene egy EXECUTE?
7

Elvileg nem, legalábbis én

inf · 2013. Júl. 4. (Cs), 09.41
Elvileg nem, legalábbis én még nem láttam úgy. Szerintem a prepare + execute-ra gondolsz
9

Nem, csak beírtam a keresőbe,

H.Z. · 2013. Júl. 4. (Cs), 09.51
Nem, csak beírtam a keresőbe, hogy pgsql return query és ott egy ilyet találtam.
10

Valószínűleg hibás volt, bár

inf · 2013. Júl. 4. (Cs), 13.03
Valószínűleg hibás volt, bár én 2 napja foglalkozom pgsql-el... :-)
13

Execute

BlaZe · 2013. Júl. 4. (Cs), 22.48
Az execute dinamikus queryk lefuttatására való.
2

setof

BlaZe · 2013. Júl. 3. (Sze), 22.16
Mondjuk a legjobb az lenne, ha automatikusan állítaná be a típust, nem nekem kéne kézzel megadni. A táblák alapján elvileg tudnia kéne...

Ez most jött át. Szerintem a setof-ot keresed.
4

Köszi, megnézem. Ezekről a

inf · 2013. Júl. 4. (Cs), 00.38
Köszi, megnézem. Ezekről a típusokról hol lehet olvasni? Valamiért átsiklottam felette, amikor a típusokat néztem, hogy table, record meg setof is létezik. Utánakeresek.
8

CREATE FUNCTION test ()

inf · 2013. Júl. 4. (Cs), 09.47
CREATE FUNCTION test ()
  RETURNS SETOF "user"
AS
  $BODY$
  BEGIN
    return query SELECT
      *
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Megvan setof-al is viszont még ott van a user_salt, amit semmiképp sem szeretnék elküldeni, mert titkos... Nem lehet valahogy szűkíteni a setof-ot?

A másik, hogy le szeretném vágni az oszlopnevekről a user_ prefix-et.

Na közben rájöttem magamtól:

CREATE FUNCTION test ()
  RETURNS TABLE (id INT, name VARCHAR, email VARCHAR)
AS
  $BODY$
  BEGIN
    return QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Nem tudom, hogy a SETOF hogy szűkíthető. A DOMAIN-t láttam típusszűkítésre, viszont ódzkodom attól, hogy külön típust hozzak létre minden egyes resultset-re. Kiszórtam kérdésnek sto-ra, hátha valaki megokosít.


Ami nekem igazából kéne az a SETOF RECORD, mert php-ben úgyis string-ként kapok meg mindent. Az a kínom, hogy nem működik:

CREATE FUNCTION test ()
  RETURNS SETOF RECORD
AS
  $BODY$
  BEGIN
    RETURN QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Azt írja, hogy column type definition kellene a php-s select * után, de így meg semmi értelme nincsen, mert ugyanúgy meg kell adni a típusokat, csak máshol... pff... gőzmozdony...
11

Sajnos a SETOF RECORD és a

inf · 2013. Júl. 4. (Cs), 15.59
Sajnos a SETOF RECORD és a REFCURSOR sem hozott megváltást. Az a helyzet, hogy pgsql-ben mindenhol meg kell adni kézzel a típusokat, ezt úgy néz ki nem lehet kikerülni, nincs automatikus típusfelismerés benne, mint mondjuk mysql-ben, ilyen szempontból primitívebb nyelv, sok más szempontból viszont jóval többet tud...

Végül itt kötöttem ki:

CREATE FUNCTION test ()
  RETURNS TABLE (id "user".user_id%TYPE, name "user".user_name%TYPE, email "user".user_email%TYPE)
AS
  $BODY$
  BEGIN
    return QUERY SELECT
      "user".user_id, "user".user_name, "user".user_email
    FROM
      "user";
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Számomra ez is elfogadható, a lényeg, hogyha típust váltok valamelyik oszlopnál, akkor ne kelljen kikeresni az összes tárolt eljárást, ahol használom, is mindegyiknél kézzel átírni. Karbantartási szempontból annál még a mysql is jobb lenne...
12

Function vs view

BlaZe · 2013. Júl. 4. (Cs), 22.44
Nem tudom szeretnél-e valami bővebbet is csinálni a belsejében később, de ha jól értelek, akkor valamilyen feltételek szerint szeretnéd lekérdezni egy tábla részhalmazát. Erre én viewt ajánlanék inkább. View-t functionre is építhetsz amúgy: 
create or replace view users_func as select user_id from list_users();
Hogy a függvényeknek definiálni kell a ki és bemeneti típusokat, az szerintem jogos. Gondolj bele abba, hogy függvény hívhat függvényt. Mibe teszed bele a visszatérési értéket, ha nem tudod milyen a típusa? És ha operátort használnál rá? És hogy fordítod le? Stb... Nekem az a fura, hogy a mysql nem így csinálja. Mondjuk a mysql sokmindent nem úgy csinál, mint kéne (pl distinct, group by). Ezért is szívnak embertelent azok, akik mysql után egy rendes adatbáziskezelő elé kerülnek.

Szóval ha előre definiált queryre szeretnél választ visszaadni, akkor view (ez performanciában is jobb). Ha komolyabb logikát szeretnél, akkor tárolt. De az már típusos. Ez előrevetíti azt is, hogy ha módosítasz egy tábla mezőjének típusán, akkor ahol azt használod, ott ezt át kell vezetni. Én ezt nem érzem problémának, ez minden típusos programozási nyelvben így van. Márpedig az sql típusos.
14

Jó tegyük fel, hogy ilyesmi a

inf · 2013. Júl. 4. (Cs), 22.57
Jó tegyük fel, hogy ilyesmi a struktúra:

- vannak felhasználók
- vannak szerepkörök amikben felhasználók vannak
- vannak jogkörök amik szerepkörökhöz vagy egyedi felhasználókhoz tartozhatnak

Szeretném lekérni egy felhasználó össze jogosultságát és szerepkörét. Szerinted ezt jobb, ha több külön kérésben csinálom, vagy valahogyan egybe teszem függvényekkel?

Nekem egyelőre az jött le, hogy sokkal egyszerűbb külön lekérni mindet, aztán php-ben összefűzni, de kíváncsi vagyok, hogy ilyen téren mire képes a postgresql...

Ez előrevetíti azt is, hogy ha módosítasz egy tábla mezőjének típusán, akkor ahol azt használod, ott ezt át kell vezetni. Én ezt nem érzem problémának, ez minden típusos programozási nyelvben így van.


Ja csak a többi típusos nyelvben tolok egy refactoring-ot, aztán mindenhol magától átírja... Az sql-ben muszáj konkrét oszlopra hivatkozni, különben "error prone" lesz a kód. Ha bármikor típust módosítok, kimaradhat 1-2 helyen, aztán utána elszáll, mert nem azonosak a típusok. Unit tesztekkel mondjuk az ilyesmi orvosolható lenne, viszont akkor is tovább tart kézzel átírogatni, mint törölni a függvényeket, aztán létrehozni újra az egyes oszlopok típusára hivatkozva. A munka is gyorsabb így, mert nem kell állandóan megnézni, hogy melyik oszlopnak mi a típusa. Nem vonom kétségbe, hogy vannak előnyei a típusosságnak, de ahogy a mellékelt ábra mutatja, hátrányai is, legalábbis sql terén.

Erre én viewt ajánlanék inkább.


Az a baj vele, hogy nem szeretnék egy deka sql-t sem látni php-ben. Jelenleg az a koncepcióm, hogy írtam egy proxy-t, amin a függvény nevek ugyanazok, mint amik az adatbázisban is vannak, aztán azokat hívogatja a paraméterekkel... Ami visszajön, arra tolok egy json_encode-ot, aztán megy is a kliens-nek, nem nagyon kell így belenyúlnom, szóval nekem eléggé bejön ez a megközelítés... Nagyon jó olyan szempontból, hogy kikerülöm vele a klasszikus relational mapping-es problémákat. Általában az szokott előjönni bonyolultabb kódoknál, hogy van több query láncban, és egymás eredményét használják fel. Ilyenkor egy csomószor oda-vissza kell típuskonvertálni, ami nagyon nem jó. Körülbelül hasonló sebesség elérhető szerintem tárolt eljárásokkal, viszont cserébe sokkal tisztább lesz a kód...
15

Szeretném lekérni egy

BlaZe · 2013. Júl. 4. (Cs), 23.52
Szeretném lekérni egy felhasználó össze jogosultságát és szerepkörét. Szerinted ezt jobb, ha több külön kérésben csinálom, vagy valahogyan egybe teszem függvényekkel?

Szerintem ez megy egy aggregált komplexebb queryvel is. Hogy melyik a gyorsabb, azt most nem merném megtippelni :) Ha jól van megírva, akkor szerintem a query gyorsabb leesz, mintha függvénnyel mókolnál. Mindenesetre a szerepkörei, meg a jogai az tuti 2 különböző result, 2 különböző query/függvény stb.

Ja csak a többi típusos nyelvben tolok egy refactoring-ot, aztán mindenhol magától átírja... Az sql-ben muszáj konkrét oszlopra hivatkozni, különben "error prone" lesz a kód. Ha bármikor típust módosítok, kimaradhat 1-2 helyen, aztán utána elszáll, mert nem azonosak a típusok.

Igen, ez tény. Erre nem tudok jobbat, de érdekel, ha valakinek van jó megoldása, ötlete rá. És igen, ezt én is úgy érzem, hogy itt csak a teszt segít. De ez amúgy se kikerülhető "jobb helyeken" :) Ha nem függvényed van, hanem kliensben (php pl) futtatsz queryt, de a query mögött megváltozik a tábla struktúrája, akkor azt teszt nélkül nem kapod el. És a tárolt is egyfajta kliens az sql fölött.

Sajnos a pg nem validálja a tároltak tartalmát, csak formailag. Oracle pl besír, ha ilyesmi előfordul.

Ezt azért nem érzem akkora problémának, mert ha a db hívások nincsenek lefedve integrációs tesztekkel, akkor ettől függetlenül is érhet meglepetés. Ami mondjuk probléma, hogy így a tároltra nem nagyon mérsz branch coverage-et pl. De ez már kicsit más tészta :)

Az a baj vele, hogy nem szeretnék egy deka sql-t sem látni php-ben. Jelenleg az a koncepcióm, hogy írtam egy proxy-t, amin a függvény nevek ugyanazok, mint amik az adatbázisban is vannak, aztán azokat hívogatja a paraméterekkel... Ami visszajön, arra tolok egy json_encode-ot, aztán megy is a kliens-nek, nem nagyon kell így belenyúlnom, szóval nekem eléggé bejön ez a megközelítés...

Ez érdekes, ezen majd még meditálok :) Én erre olyat csináltam, hogy az egyes lekérdezéseket fileban tároltam, és nem a queryt tettem be a kód közepére, hanem egy new SQLFile(...)-t. Syntax highlightolható, formázható stb. Php-ban az is megoldható, hogy naming convention alapján keresi ki a filet metódushívás alapján, és a paramétereket egyszerűen bedobálja a querynek. Így még tárolt se kell. Cserébe a típus módosításos probléma itt is fennáll :)

De amúgy ha jól érzem, akkor ezt a koncepciódat php oldalon az nem borítja sehol, hogy a pg típust vár függvénynél visszatérési értékként.
16

Ja, jól érzed... :-) Nekem

inf · 2013. Júl. 5. (P), 09.28
Ja, jól érzed... :-) Nekem egyelőre ez tűnik a legtisztább megoldásnak. A második az, hogy egy ORM-el mindent elfedek, amit csak lehet...

Szerintem ez megy egy aggregált komplexebb queryvel is.


Ha egy ilyet tárolt eljárásba teszek, akkor meg lehet oldani, hogy amikor php-ban fetch-elem, akkor valami ilyesmit kapjak, és ne stringbe összefűzött dolgokat? (json-ban írom, úgy tömörebb)

{
	name: "Adminisztrátor",
	members: [
		{id: 1, name: "Kovács Pista"},
		{id: 2, name: "Tóth Annamária"}
	],
	permissions: [
		{id: 1, method: "GET", resource: "^/$"},
		{id: 2, method: "GET", resource: "^/user$"}
	]
}
Ha igen, akkor elgondolkodom rajta, hogy beteszem ezt is adatbázisba, és tényleg csak egy json_encode lesz a php-ban, meg majd a bemenet ellenőrzése.
17

JSON

BlaZe · 2013. Júl. 5. (P), 20.45
Ha egy ilyet tárolt eljárásba teszek, akkor meg lehet oldani, hogy amikor php-ban fetch-elem, akkor valami ilyesmit kapjak, és ne stringbe összefűzött dolgokat? (json-ban írom, úgy tömörebb)

Ha 9.2-es pg van alattad, akkor határozottan, abban van json típus :)

Amúgy szerintem saját típussal csinálhatsz hasonlót.
18

Az a gáz, hogy 8.4 van. Saját

inf · 2013. Júl. 5. (P), 22.26
Az a gáz, hogy 8.4 van. Saját típusokkal hogy lehet megoldani? Írni kell külön aggregate függvényt?

szerk:

Van egy json modul, amit esetleg lehetne telepíteni 8.4-re, de árban nem éri meg, és ránézésre nem támogat komplexebb kompozit típusokat. Az egész pgsql-ben elég nehézkesen lett megoldva a típusosság. Bizonyos esetekben van automatikus konverzió: SETOF RECORD <-> TABLE, INT -> TABLE.ROW.COLUMN stb... Bizonyos esetekben meg nincs. Szóval egyáltalán nem egyértelmű, hogy valaminek éppen mi a típusa, vagy hogy két eltérő típus között automatikus konverzió van e vagy sem, stb... Hiányzik egy csomó felületi függvény is, ez mondjuk talán inkább a php-s csatoló hibája, de jó lenne, ha tömböket automatikusan konvertálna, lenne asszociatív array a saját típusok helyett, stb... Jelenleg szerintem még a 9.2-es verzióval is csak nehézkesen lehetne megoldani a problémát. Én személy szerint a kompozit típusok helyett inkább maradok a több kérés küldésénél, és a php-s összefűzésnél bonyolultabb adat formára, mert ott mindez maradéktalanul megoldható.


http://stackoverflow.com/questions/11940720/returning-a-nested-composite-type-from-a-pl-pgsql-function
Itt megcsinálják gyakorlatilag ugyanazt, de mivel nincs asszociatív típus, ezért sima tömböket ad csak vissza, és utólag még azt is fel kell dolgozni. Ez így kb használhatatlan, ha valaki automatikus átalakítást akar...