Sending one record from cursor to another function Postgres

Question

FYI: I am completely new to using cursors... So I have one function that 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 I call it and need to process 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 select from the cursor, loop using fetch (next is assumed correct?) and put the record fetched into this_promotion. Then send the record in this_promotion to another function. I can't figure out what to declare the type of this_promotion in get_best_product_promos. Here is 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 type record

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 a function being created w/ a input parameter that had a type 'tablename' this works, but it has to be a tablename not a function :( I know I am so close, I was told to use cursors to pass records around. So I studied up. Please help.

Answer 1

The answer:

Select specific fields in the 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...

Answer 2

One possibility would be to define the query you have in get_all_product_promos as a all_product_promos view instead. Then you would automatically have the "all_product_promos%rowtype" type 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 verify using EXPLAIN that querying SELECT * FROM all_product_promos WHERE object_id = ? takes the object_id parameter into the two subqueries rather than filtering afterwards. 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 avoid using cursors to pass records around in PLPGSQL. In fact, I would avoid using cursors in PLPGSQL full stop- unless you need to pass a whole resultset to another function for some reason. This method of simply looping through the statement is much simpler, with the caveat that the entire resultset is materialised into memory first.

The other disadvantage with this approach is that if you need to add a column to all_product_promos, you will need to recreate all the functions that depend on it, since you can't add columns to a view with 'alter view'. AFAICT this affects named types create with CREATE TYPE too, since ALTER TYPE doesn't seem to allow you to add columns to a type either.

So, you can specify a record format using "CREATE TYPE" to pass between functions. Any relation automagically specifies a type called <relation>%ROWTYPE which you can also use.