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';
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.
You never assigned to the variables
driver_contract, so of course the result columns are all NULL.
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 🙂