0

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.

1 Answer 1

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.