[PostgreSQL] Recursive tree function
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE category
(
category_id bigserial NOT NULL,
category_parent_id bigint NULL DEFAULT NULL,
category_url character varying(255) NOT NULL,
category_url_part character varying(100) NOT NULL,
category_name character varying(100) NOT NULL,
category_title character varying(100),
category_description character varying(200),
category_changed timestamp with time zone,
category_added timestamp with time zone NOT NULL DEFAULT now(),
category_publish boolean NOT NULL DEFAULT false,
CONSTRAINT category_pk PRIMARY KEY (category_id),
CONSTRAINT category_unique_url UNIQUE (category_parent_id, category_url)
)
WITH (OIDS=TRUE);
ALTER TABLE category OWNER TO postgres;
(
category_id bigserial NOT NULL,
category_parent_id bigint NULL DEFAULT NULL,
category_url character varying(255) NOT NULL,
category_url_part character varying(100) NOT NULL,
category_name character varying(100) NOT NULL,
category_title character varying(100),
category_description character varying(200),
category_changed timestamp with time zone,
category_added timestamp with time zone NOT NULL DEFAULT now(),
category_publish boolean NOT NULL DEFAULT false,
CONSTRAINT category_pk PRIMARY KEY (category_id),
CONSTRAINT category_unique_url UNIQUE (category_parent_id, category_url)
)
WITH (OIDS=TRUE);
ALTER TABLE category OWNER TO postgres;
Het veld category_url wil ik gaan vullen met de volledige url van de category. Door een functie aan te maken en door de tree structure te itereren zoek ik de volledige url. Om performance redenen wil ik deze gaan opslaan en bijwerken d.m.v. triggers.
Het lukt me alleen nog niet om de functie werkend te krijgen, dit heb ik to nu toe:
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 category_url(bigint)
RETURNS text AS
$BODY$
DECLARE
child_id ALIAS FOR $1;
url TEXT;
category_tmp RECORD;
BEGIN
SELECT category_parent_id, category_url_part INTO category_tmp FROM category WHERE category_id = child_id;
IF(category_tmp.category_parent_id IS NOT NULL) THEN
url := category_url(category_tmp.category_parent_id) || '/' || url;
ELSE
url := category_tmp.category_url_part || '/' || url;
END IF;
RETURN url;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION category_url(bigint) OWNER TO postgres;
RETURNS text AS
$BODY$
DECLARE
child_id ALIAS FOR $1;
url TEXT;
category_tmp RECORD;
BEGIN
SELECT category_parent_id, category_url_part INTO category_tmp FROM category WHERE category_id = child_id;
IF(category_tmp.category_parent_id IS NOT NULL) THEN
url := category_url(category_tmp.category_parent_id) || '/' || url;
ELSE
url := category_tmp.category_url_part || '/' || url;
END IF;
RETURN url;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION category_url(bigint) OWNER TO postgres;
Bij een select query krijg ik een leeg resultaat terug. De waarden van de select into kan ik wel returnen.
Heb helaas nog weinig ervaring met PostgreSQL. Na uren zoeken hoop ik dat iemand mij snel kan helpen, anders duik ik er opnieuw in ;)
Thnx in advance!
Gewijzigd op 01/01/1970 01:00:00 door Gerron
Er zijn nog geen reacties op dit bericht.