PL/PGSQL return row met kolomnamen?

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

TJVB tvb

TJVB tvb

28/12/2007 16:34:00
Quote Anchor link
Hallo iedereen,
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)
PHP script in nieuw venster Selecteer het PHP script
1
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;


Alvast bedankt voor de hulp
 
PHP hulp

PHP hulp

08/05/2021 16:32:03
 
Frank -

Frank -

28/12/2007 16:55:00
Quote Anchor link
Werk met IN en OUT parameters en werk nooit met een *, dat heb je niet nodig en is (ietsjes) langzamer.

Voorbeeldje van een api uit een testdatabase:
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
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;

Deze sp roept weer sp's in onderliggende schema's aan, nooit rechtstreeks de tabellen. Dat is verboden terrein.
 
TJVB tvb

TJVB tvb

28/12/2007 17:27:00
Quote Anchor link
Die IN en OUT parameters zal ik eens bekijken.
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?
 
Frank -

Frank -

28/12/2007 17:48:00
Quote Anchor link
Wanneer je nu een wijziging in de tabel maakt, kan zo maar ineens ergens een sp in het honderd lopen. Er zit namelijk geen logisch verband tussen de sp en de tabel (-len) die jij in de sp aanroept.

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.
 
TJVB tvb

TJVB tvb

28/12/2007 18:20:00
Quote Anchor link
Dat is wel heel duidelijk, zo heb ik het nog nooit bekeken.

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
 
Frank -

Frank -

28/12/2007 18:37:00
Quote Anchor link
De sp api.list_roles() kent de volgende parameters:
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)
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
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;

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 -
 
TJVB tvb

TJVB tvb

28/12/2007 20:01:00
Quote Anchor link
Dit is een hoop informatie, ik heb het nog niet helemaal te pakken maar zal morgen eens verder kijken.
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)
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
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;

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
 



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.