Ruzie met PL/pgSQL [solved]

Door PHP erik op 28-08-2006 17:49

2.309 views

Ik heb nu officieel ruzie met PL/pgSQL.

Ik heb de volgende twee tabellen:

tabel 'elements'
- id SERIAL
- element VARCHAR

tabel 'styles'
- id SERIAL
- style TEXT
- element INTEGER (FOREIGN KEY -> elements(id))

Nu komen sommige 'style'-s uit de tabel 'styles' meerdere malen voor, maar met verschillende elementen.

Ik voer deze query uit en zo wil ik het hebben, werkt perfect:

SELECT style FROM styles GROUP BY style ORDER BY style

Maar, nu wil ik ook netjes een met komma gescheiden lijstje van alle 'element'-s die deze 'style' hebben. Voor zover ik weet kun je niet een subquery concatten, dus schrijf ik er even een functie voor. Ik kom in eerste instantie hier op uit:

CREATE FUNCTION get_all(text) RETURNS text '
DECLARE
style_text ALIAS FOR $1;
elements_text TEXT;
row_data record;
BEGIN
FOR row_data IN (SELECT element FROM elements WHERE id IN ( SELECT element FROM styles WHERE style = style_text)) LOOP
elements_text := elements_text || row_data.element || '', '';
END LOOP;
RETURN elements_text;
END;
' LANGUAGE 'plpgsql';

[edit]De query binnen de functie levert wel het juiste resultaat op als ik 'm los uitvoer:

SELECT element FROM elements WHERE id IN ( SELECT element FROM styles WHERE style = 'color: #FFF;')

Namelijk:

element
---------
a:link
a:hover
body
#main
etc
[/edit]

Als ik nu deze functie uitvoer en er wel resultaten zijn, dan krijg ik:

SQL fout:

ERROR: query "SELECT $1 || $2 || '', ''" returned 2 columns
CONTEXT: PL/pgSQL function "get_all" line 7 at assignment

In statement:
SELECT get_all(style) FROM styles

Of als ik logischerwijs wat dingen wijzig gaat 'ie klagen over een verkeerde integerwaarde, terwijl het juist met strings werkt. Erg raar.

Nu kan ik 'm ook herschrijven, maar dan krijg ik geen error, maar gewoon over NULL:

DECLARE
style_text ALIAS FOR $1;
elements_text TEXT;
elements_this TEXT;
elements_current RECORD;
BEGIN
FOR elements_current IN SELECT element FROM styles WHERE style = style_text LOOP
SELECT INTO elements_this element FROM elements WHERE id = elements_current.element;
elements_text := elements_text || elements_this;
END LOOP;
RETURN elements_text;
END;

Voor de duidelijkheid, ik gebruik dus:

SELECT style, get_all(style) FROM styles GROUP BY style ORDER BY style

Ik werk eigenlijk nooit met het uitlezen van rows via PL/pgSQL, dus misschien dat ik iets over het hoofd zie dat een ervaren FOR-INner wel ziet.

Alvast bedankt voor jullie tijd,

Erik

[edit]Script heeft te maken met CSS generator voor een grote website.[/edit]

Jan Koehoorn

28-08-2006 18:07

SELECT s.style, GROUP_CONCAT( e.element ) AS element_list
FROM styles AS s, elements AS e
WHERE s.element_id = e.id
GROUP BY style
ORDER BY style ASC
[edit]
alias toegevoegd
[/edit]

PHP erik

28-08-2006 18:17

SQL fout:

ERROR: function group_concat(character varying) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

In statement:
SELECT s.style, GROUP_CONCAT( e.element ) AS element_list
FROM styles AS s, elements AS e
WHERE s.element_id = e.id
GROUP BY style
ORDER BY style ASC

Jan Koehoorn

28-08-2006 18:17

Jammer, ik hoop dat PostGre ook wel een group_concat zou hebben :-(

PHP erik

28-08-2006 18:19

Hmm, er moet toch echt zoiets bestaan, effe zoeken hoor.

Jan Koehoorn

28-08-2006 18:23

Zoiets: klik

Het werkt wel, lokaal getest:

PHP erik

28-08-2006 18:30

Ik heb zelf net ondertussen ook alles werkend gekregen met:

SELECT TRIM(', ' FROM CONCAT((SELECT element FROM elements WHERE id = styles.element)||', ')), style FROM styles GROUP BY style

PHP erik

28-08-2006 18:32

Jan Koehoorn schreef op 28.08.2006 18:23
Zoiets: klik

Het werkt wel, lokaal getest:

Dit is toch MySQL?

Jan Koehoorn

28-08-2006 18:36

Ja klopt. Ik heb geen PostGre (wel gedownload gisteren, naar aanleiding van het topic van de afgelopen dagen) en ging er van uit dat GROUP_CONCAT ook wel in PostGre zou bestaan.

PHP erik

28-08-2006 19:10

De reden dat m'n originele procedure het niet deed is trouwens omdat het stomme phpPgAdmin een beetje speelt met enkele quotes, waardoor de spatie niet pakte. Normaal doe ik dit in de terminal van de Mac en werkt het wel. Dus nu weet ik in ieder geval waarom die steeds NULL gaf, de output-var was namelijk leeg en kon daardoor niet "output-var = output-var en_nog_iets" doen.

Thanks Jan voor je nuttige reacties.

PHP erik

28-08-2006 19:12

Dit werkt namelijk wel, let op regel 3 (DECLARE is 1):

DECLARE
style_text ALIAS FOR $1;
elements_text TEXT = ' ';
elements_this TEXT;
elements_current RECORD;
BEGIN
FOR elements_current IN SELECT element FROM styles WHERE style = style_text LOOP
SELECT INTO elements_this element FROM elements WHERE id = elements_current.element;
elements_text := elements_text || ', ' || elements_this;
END LOOP;
RETURN elements_text;
END;

Reageren

Inloggen om te reageren