PL/PGSQL return row met kolomnamen?
Ik ben zelf nog niet heel lang met postgresql en pl/pgsql bezig. Tot nu toe had ik wat simpele functies.
Ik heb 2 schema's, 1 waar de data in staat en waar alleen een DB admin user toegang tot heeft. En 1 waar de functies in staan waar de account voor de website toegang tot heeft.
Nu wil ik graag een data row terug geven door middel van een functie. Ik krijg wel een array met waardes alleen geen enkele kolomnaam. Ik zou graag ook de kolomnamen meekrijgen net zoals met een gewone SELECT query.
Mijn vraag is alleen of dit mogelijk is en als het mogelijk is hoe?
Ik heb namenlijk de manual doorgelezen, een boek erbij gepakt en gezocht met google maar kwam er niet uit.
De functie:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
/*The function to load the access profile from a user*/
CREATE OR REPLACE FUNCTION functions.load_access_profile(TEXT) RETURNS RECORD AS $$
DECLARE
nickname ALIAS FOR $1;
access_row data.accessprofile%ROWTYPE;
BEGIN
SELECT INTO access_row data.accessprofile.*
FROM data.accessprofile,data.users
WHERE data.accessprofile.id=data.users.accessprofile AND username=nickname;
RETURN access_row;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
CREATE OR REPLACE FUNCTION functions.load_access_profile(TEXT) RETURNS RECORD AS $$
DECLARE
nickname ALIAS FOR $1;
access_row data.accessprofile%ROWTYPE;
BEGIN
SELECT INTO access_row data.accessprofile.*
FROM data.accessprofile,data.users
WHERE data.accessprofile.id=data.users.accessprofile AND username=nickname;
RETURN access_row;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
Alvast bedankt voor de hulp
Voorbeeldje van een api uit een testdatabase:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION api.list_roles(IN role_status text, OUT code text, OUT name text, OUT status text, OUT system_name text, OUT "level" integer)
RETURNS SETOF record AS
$BODY$
DECLARE
row record;
BEGIN
FOR row IN SELECT * FROM role.list(role_status)
LOOP
code := row.code;
name := row.name;
status := row.status;
system_name := row.system_name;
level := row.level;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
RETURNS SETOF record AS
$BODY$
DECLARE
row record;
BEGIN
FOR row IN SELECT * FROM role.list(role_status)
LOOP
code := row.code;
name := row.name;
status := row.status;
system_name := row.system_name;
level := row.level;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Deze sp roept weer sp's in onderliggende schema's aan, nooit rechtstreeks de tabellen. Dat is verboden terrein.
Maar wat is er mis om de sp's direct met de tabellen de laten communiceren? Als de gebruiker alleen maar die sp's kan aanroepen?
In mijn voorbeeld is er sprake van een SCHEMA api en een SCHEMA role. In het SCHEMA role staan ook alle tabellen, domeinen, triggers, functies, etc. wat allemaal bij het object 'role' hoort. Wanneer ik nu een wijziging aanbreng aan dit object, bv. een kolom uit een tabel verwijder, dan hoef ik alleen maar dit schema na te lopen en eventueel nog wat andere wijzigingen te maken. In mijn databases is er niet 1 schema (api uitgezonderd) te vinden die gegevens opvraagt uit een ander schema. Komt gewoon niet voor.
Wanneer diverse schema's gegevens met elkaar moeten delen, dan loopt dat via api die keurig de diverse functies/sp's in de diverse schema's aanroept. Functies/sp's in een object (lees schema) roepen wel de tabellen in dit object (schema) aan.
Ik ben gek op spagetti, maar niet in mijn code. Dat is niet te bouwen, debuggen of te onderhouden.
Ps. Met kleine systemen zijn de voordelen minder groot of zelfs afwezig, maar kleine systemen worden ook groot... Bovenstaand voorbeeld is oorspronkelijk ontworpen voor een systeem waar uiteindelijk meer dan 1100 sp's in zitten, verspreid over een kleine 60 schema's met daarin totaal 180 tabellen.
Ik denk dat ik eens wat ga wijzigen in het ontwerp en kijken wat voor invloed dit verder heeft.
Maar bij de code had ik nog wel een vraag.
zijn code,name,status,system,level dan de kolomnamen of zijn dat speciale eigenschappen in:
code := row.code;
name := row.name;
status := row.status;
system_name := row.system_name;
level := row.level;
Ik ben dan namenlijk nieuwsgierig hoe je dat in role.list(role_status) doet
IN role_status text,
OUT code text,
OUT name text,
OUT status text,
OUT system_name text,
OUT "level" integer
De IN-parameter wordt gebruikt om de sp role.list() van de juiste IN-parameter te voorzien, deze wordt op regel 7 aangeroepen in de LOOP. Het resultaat van deze sp wordt in de variabele 'row' gezet.
Vervolgens worden in de LOOP de records aangemaakt die samen de output van de sp api.list_roles() vormen. De parameter 'code' (zie de OUT-parameter!) wordt gevuld met de waarde in row.code, de parameter 'name' wordt gevuld met de waarde in row.name, etc. etc. Nadat de parameters van een waarde zijn voorzien, wordt een RETURN NEXT uitgevoerd en het volgende record aangemaakt. Mocht die er zijn, anders stopt de LOOP.
Wanneer je slechts 1 record retour verwacht, is het natuurlijk niet nodig om een SETOF en een LOOP te gebruiken, maar het kan wel.
Jouw SCHEMA-naam 'functions' is wat raar gekozen, in pgSQL kan ieder SCHEMA functies bevatten, dat is niks bijzonders, dat is bij mij zelfs standaard. api is dan een betere naam, dat is tenslotte de Application Programming Interface voor de PHP/.NET/JSP-programmeur die jouw pgSQL-database wil gebruiken.
Edit: Ik zie net dat ik hier gruwelijk door de mand ben gevallen: 'level' is een gereserveerd woord in de SQL-standaard is dan ook keurig met quotes (lees: backtics) omzeild. In pgSQL is het overigens géén gereserveerd woord, de quotes zijn dan ook niet nodig. Geen idee waarom die er dan toch in terecht zijn gekomen.
Edit 2: Hier nog even de sp role.list():
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
30
31
32
33
34
35
36
37
38
39
40
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
30
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE FUNCTION "role".list(IN "show" text, OUT id_role integer, OUT code text, OUT name text, OUT system_name text, OUT "level" integer, OUT status text)
RETURNS SETOF record AS
$BODY$
DECLARE
row record;
status_vector text[];
today timestamp;
command text := '';
BEGIN
today := NOW();
status_vector := '{"active","inactive"}';
IF (show = 'all') THEN
command := command ||'SELECT ';
command := command ||'r.* ';
command := command ||'FROM ';
command := command ||'role.role AS r ';
ELSIF (show = ANY (status_vector)) THEN
command := command ||'SELECT ';
command := command ||'r.* ';
command := command ||'FROM ';
command := command ||'role.role AS r ';
command := command ||'WHERE ';
command := command ||'(r.status = '||QUOTE_LITERAL(show)||')';
ELSE
command := 'SELECT * FROM role.role LIMIT 0';
END IF;
FOR row IN EXECUTE command
LOOP
id_role := row.id;
code := row.code;
name := row.name;
system_name := row.system_name;
level := row.level;
status := row.status;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
RETURNS SETOF record AS
$BODY$
DECLARE
row record;
status_vector text[];
today timestamp;
command text := '';
BEGIN
today := NOW();
status_vector := '{"active","inactive"}';
IF (show = 'all') THEN
command := command ||'SELECT ';
command := command ||'r.* ';
command := command ||'FROM ';
command := command ||'role.role AS r ';
ELSIF (show = ANY (status_vector)) THEN
command := command ||'SELECT ';
command := command ||'r.* ';
command := command ||'FROM ';
command := command ||'role.role AS r ';
command := command ||'WHERE ';
command := command ||'(r.status = '||QUOTE_LITERAL(show)||')';
ELSE
command := 'SELECT * FROM role.role LIMIT 0';
END IF;
FOR row IN EXECUTE command
LOOP
id_role := row.id;
code := row.code;
name := row.name;
system_name := row.system_name;
level := row.level;
status := row.status;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Dit is ook weer een fraaie sp, deze werkt met een array status_vector en stelt een dynamische query samen, command. Deze sp is trouwens voor verbetering vatbaar, ik zou hem nu iets anders opbouwen. Voortschrijdend inzicht.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
De IN en OUT parameters in combinatie met de Loop zijn voor mij nieuw.
Maar alvast bedankt voor de informatie.
Edit
Ik heb nu deze functie:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE FUNCTION data.load_access_profile(IN nickname TEXT, OUT id integer, OUT name text)
RETURNS SETOF record AS
$BODY$
DECLARE
row RECORD;
command TEXT := '';
BEGIN
command := 'SELECT data.accessprofile.*
FROM data.accessprofile,data.users
WHERE data.accessprofile.id=data.users.accessprofile AND username='|| QUOTE_LITERAL(nickname);
FOR row IN EXECUTE command
LOOP
id := row.id;
name := row.name;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;
RETURNS SETOF record AS
$BODY$
DECLARE
row RECORD;
command TEXT := '';
BEGIN
command := 'SELECT data.accessprofile.*
FROM data.accessprofile,data.users
WHERE data.accessprofile.id=data.users.accessprofile AND username='|| QUOTE_LITERAL(nickname);
FOR row IN EXECUTE command
LOOP
id := row.id;
name := row.name;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;
En deze werkt.
Bedankt voor de informatie, nu kan ik verder. Maar ik zal eerst eens mijn data en functie ontwerp eens doornemen voor verbeteringen.
De duidelijkere verdeling over de verschillende schema's klinkt wel interessant
Gewijzigd op 01/01/1970 01:00:00 door TJVB tvb