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 YourEmailAddressHere" to ma*******@postgresql.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_products_for_order(integer) 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_added;
/*
* Record all basic products in the given order number
*/
INSERT INTO GBPFO_products_list
SELECT basic_product_id,
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_added
SELECT
general_products.id,
false,
false,
true
FROM
general_products,
GBPFO_products_list
WHERE
GBPFO_products_list.basic_product_id = general_products.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_added;
DELETE FROM GBPFO_to_be_added;
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_added
SELECT
specific_products.id,
false,
false,
true
FROM
specific_products,
GBPFO_products_list
WHERE
GBPFO_products_list.basic_product_id = specific_products.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_added;
DELETE FROM GBPFO_to_be_added;
SELECT INTO after COUNT(*) FROM GBPFO_products_list;
EXIT WHEN before = after;
END LOOP;
/*
* Output the required result.
*/
FOR
reg
IN
SELECT general_products.id,
general_products.parent_id,
general_products.description,
0.00
FROM GBPFO_products_list,
general_products
WHERE GBPFO_products_list.is_specific = false
AND GBPFO_products_list.in_order = false
AND GBPFO_products_list.basic_product_id = general_products.id
UNION
SELECT specific_products.id,
specific_products.parent_id,
specific_products.description,
specific_products.unit_price
FROM GBPFO_products_list,
specific_products
WHERE GBPFO_products_list.is_specific = true
AND GBPFO_products_list.in_order = false
AND GBPFO_products_list.basic_product_id = specific_products.id
LOOP
RETURN NEXT reg;
END LOOP;
RETURN;
END;
'
LANGUAGE plpgsql;
