Sending one entry from cursor to another Postgres function
FYI: I am completely unfamiliar with cursors ... So I have one function, which is a cursor:
CREATE FUNCTION get_all_product_promos(refcursor, cursor_object_id integer) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT *
FROM promos prom1
JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id)
WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date
AND promo_objects.object_id = cursor_object_id
UNION
SELECT prom2.promo_id
FROM promos prom2
JOIN promo_buy_objects ON (prom2.promo_id =
promo_buy_objects.promo_id)
LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id
WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0) AND
prom2.active = true AND now() BETWEEN prom2.start_date AND
prom2.end_date AND promo_buy_objects.object_id = cursor_object_id;
RETURN $1;
END;
' LANGUAGE plpgsql;
SO, then in another function that I call and you need to handle it:
...
--Get the promotions from the cursor
SELECT get_all_product_promos('promo_cursor', this_object_id)
updated := FALSE;
IF FOUND THEN
--Then loop through your results
LOOP
FETCH promo_cursor into this_promotion
--Preform comparison logic -this is necessary as this logic is used in other contexts from other functions
SELECT * INTO best_promo_results FROM get_best_product_promos(this_promotion, this_object_id, get_free_promotion, get_free_promotion_value, current_promotion_value, current_promotion);
...
So the idea here is to fetch from the cursor, loop using fetch (is the next one considered correct?) And put the entry that you got into this_promotion. Then post the entry in this_promotion to another function. I cannot figure out what to declare the type this_promotion in get_best_product_promos. Here's what I have:
CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion record, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer)
RETURNS...
It tells me: ERROR: plpgsql functions cannot take a record like
OK first I tried:
CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion get_all_product_promos, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer)
RETURNS...
Because I saw some syntax in the Postgres docs, showed that the function is created with an input parameter that is of type "tablename", this works, but it should be tablename, not a function :( I know I'm so close, I was told use cursors to write records, so I learned. Please help.
a source to share
One possibility would be to define the request you have in get_all_product_promos as the all_product_promos view. Then you will automatically have type "all_product_promos% rowtype" to pass between functions.
That is, something like:
CREATE VIEW all_product_promos AS
SELECT promo_objects.object_id, prom1.*
FROM promos prom1
JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id)
WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date
UNION ALL
SELECT promo_buy_objects.object_id, prom2.*
FROM promos prom2
JOIN promo_buy_objects ON (prom2.promo_id = promo_buy_objects.promo_id)
LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id
WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0)
AND prom2.active = true
AND now() BETWEEN prom2.start_date AND prom2.end_date
You should be able to check with EXPLAIN that the query SELECT * FROM all_product_promos WHERE object_id = ?
takes a parameter object_id
in two subqueries and not after filtering. Then, from another function, you can write:
DECLARE
this_promotion all_product_promos%ROWTYPE;
BEGIN
FOR this_promotion IN
SELECT * FROM all_product_promos WHERE object_id = this_object_id
LOOP
-- deal with promotion in this_promotion
END LOOP;
END
TBH I would not use cursors to pass records to PLPGSQL. In fact, I would not use cursors in PLPGSQL full stop unless you need to pass a whole set of results to another function for some reason. This method of simply scrolling a statement is much easier, with the caveat that the entire result set is first materialized into memory.
Another disadvantage of this approach is that if you need to add a column for all_product_promos, you will need to recreate all the functions that depend on it, since you cannot add columns to the view using the "alter view". AFAICT affects named types by creating using CREATE TYPE
too, since ALTER TYPE
it doesn't seem to allow columns to be added to the type.
Thus, you can specify the recording format using "CREATE TYPE" for passing functions. Any relationship automatically defines a type called <relation>%ROWTYPE
which you can also use.
a source to share
Answer:
Select specific fields in cursor function instead of *
then
CREATE TYPE get_all_product_promos as (buy_quantity integer, discount_amount numeric(10,2), get_quantity integer, discount_type integer, promo_id integer);
Then I can say:
CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion get_all_product_promos, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer)
RETURNS...
a source to share