Matthew Metnetsky wrote:
I'm trying to write two custom recursive functions: one that selects,
and the other that selects/updates.
I've been looking around the internet for examples for they're so
limiting and completely off track from what I need to do. The best
examples I can find is in the source code for Postgres, but that's so
complicated I barely have an idea whats going on with its limited
comments.
So, does anyone know of (or have) good examples of queries within
functions (and returning sets of data)?
Thanks in advance,
~ Matthew
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
I've just been doing the same sort of thing using plpgsql. Here's one of
the ones I did this morning (sorry, no DDL, but it gives you an idea of
some syntax).
john
PS. I've been using this for less than a week so hold back on the abuse
please.
CREATE OR REPLACE FUNCTION get_basic_produ cts_for_order(i nteger) RETURNS
setof RECORD AS
'
DECLARE
reg RECORD;
input_order_id ALIAS FOR $1;
before int;
after int;
BEGIN
/*
* Prepare tables for use.
*/
DELETE FROM GBPFO_products_ list;
DELETE FROM GBPFO_to_be_add ed;
/*
* Record all basic products in the given order number
*/
INSERT INTO GBPFO_products_ list
SELECT basic_product_i d,
false,
true,
true
FROM
orderline_leaf_ items
WHERE
order_id = input_order_id;
/*
* Iterate through the list, adding general products that are
children of existing
* products.
*/
LOOP
SELECT INTO before COUNT(*) FROM GBPFO_products_ list;
INSERT INTO GBPFO_to_be_add ed
SELECT
general_product s.id,
false,
false,
true
FROM
general_product s,
GBPFO_products_ list
WHERE
GBPFO_products_ list.basic_prod uct_id = general_product s.parent_id
AND
GBPFO_products_ list.active = true;
UPDATE GBPFO_products_ list
SET active = false;
INSERT INTO GBPFO_products_ list
SELECT * FROM GBPFO_to_be_add ed;
DELETE FROM GBPFO_to_be_add ed;
SELECT INTO after COUNT(*) FROM GBPFO_products_ list;
EXIT WHEN before = after;
END LOOP;
/*
* Iterate through the list, adding specific products that are
children of existing
* products.
*/
UPDATE GBPFO_products_ list SET active = true;
LOOP
SELECT INTO before COUNT(*) FROM GBPFO_products_ list;
INSERT INTO GBPFO_to_be_add ed
SELECT
specific_produc ts.id,
false,
false,
true
FROM
specific_produc ts,
GBPFO_products_ list
WHERE
GBPFO_products_ list.basic_prod uct_id = specific_produc ts.parent_id
AND
GBPFO_products_ list.active = true;
UPDATE GBPFO_products_ list
SET active = false;
INSERT INTO GBPFO_products_ list
SELECT * FROM GBPFO_to_be_add ed;
DELETE FROM GBPFO_to_be_add ed;
SELECT INTO after COUNT(*) FROM GBPFO_products_ list;
EXIT WHEN before = after;
END LOOP;
/*
* Output the required result.
*/
FOR
reg
IN
SELECT general_product s.id,
general_product s.parent_id,
general_product s.description,
0.00
FROM GBPFO_products_ list,
general_product s
WHERE GBPFO_products_ list.is_specifi c = false
AND GBPFO_products_ list.in_order = false
AND GBPFO_products_ list.basic_prod uct_id = general_product s.id
UNION
SELECT specific_produc ts.id,
specific_produc ts.parent_id,
specific_produc ts.description,
specific_produc ts.unit_price
FROM GBPFO_products_ list,
specific_produc ts
WHERE GBPFO_products_ list.is_specifi c = true
AND GBPFO_products_ list.in_order = false
AND GBPFO_products_ list.basic_prod uct_id = specific_produc ts.id
LOOP
RETURN NEXT reg;
END LOOP;
RETURN;
END;
'
LANGUAGE plpgsql;
