Cursor doesn't fetch values correctly in Postgresql

All we need is an easy explanation of the problem, so here it is.

Here is the table that I wanna fetch info from:

CREATE TABLE public.insurance_premiums (
    contract_code text NOT NULL,
    insurance_team text NOT NULL,
    starting_date date NOT NULL,
    expiration_date date NOT NULL,
    contract_cost float8 NOT NULL,
    vehicle_contract text NOT NULL,
    customer_contract text NOT NULL,
    driver_contract text NOT NULL,
    CONSTRAINT insurance_premiums_pkey PRIMARY KEY (contract_code)
);

Here is the Cursor:

CREATE OR REPLACE FUNCTION get_info (s_d_1 DATE, s_d_2 DATE)
RETURNS TABLE (
    contract_code text,
    customer_contract text,
    driver_contract text
) AS $$
    declare
    new_contract record;
    code text;
    customer text;
    driver text;

    new_contract_cursor CURSOR (s_d_1 DATE, s_d_2 DATE) FOR
        select insurance_premiums.contract_code, insurance_premiums.customer_contract, insurance_premiums.driver_contract 
        from public.insurance_premiums 
        where starting_date >= s_d_1 and starting_date <= s_d_2;
        
        begin
            
            open new_contract_cursor(s_d_1, s_d_2);
            
            loop
                fetch new_contract_cursor into new_contract;
                exit when not found;
                code := new_contract.contract_code;
                customer := new_contract.customer_contract;
                driver := new_contract.driver_contract;
                return next;
            end loop;
    
            close new_contract_cursor;
    
        end;$$ language plpgsql;

Note: s_d_1 stands for starting_date_1 same goes for s_d_2.

Here is also the simple SELECT query:

select insurance_premiums.contract_code, insurance_premiums.customer_contract, insurance_premiums.driver_contract from public.insurance_premiums 
where starting_date >= '2021/06/01' and starting_date <= '2021/06/30';

This SELECT query fetches the correct data from the table. It simply returns 2 rows with 3 columns that are all text.

The cursor on the other hand, even though it creates a new table and correctly fetches 2 rows and 3 columns all the data is NULL instead of text.

I’m guessing that something is wrong inside the loop or in the select query inside the cursor.

I also thought about the fact that I create a new table with column names being the same from the table I fetch might have to do anything. I don’t think this is it but you never know.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You never assigned to the variables contract_code, customer_contract and driver_contract, so of course the result columns are all NULL.

Use

get_info.contract_code := new_contract.contract_code;
get_info.customer_contract := new_contract.customer_contract;
get_info.driver_contract := new_contract.driver_contract;
RETURN NEXT;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply