Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table payment ( payment_id int, rental_id int, amount numeric(5,2) )

CREATE TABLE

insert into payment values ( 1 , 1, 100 )

INSERT 0 1

insert into payment values ( 2, 2, 200 )

INSERT 0 1

select * from payment

payment_id
rental_id
amount

1
1
100.00

2
2
200.00

SELECT 2

CREATE OR REPLACE FUNCTION get_data(currency_type text) 
RETURNS TABLE  ( payment_id int, rental_id int, amount numeric(5,2) ) 
language plpgsql
as $$
begin 
   return query 
     SELECT b.payment_id, b.rental_id, 
    case 
        when currency_type = 'EUR' then b.amount * 1.16     
        when currency_type = 'RMB' then b.amount * 6.44 
        when currency_type = 'USD' then b.amount 
    end as amount 
    FROM payment b;
end;$$

CREATE FUNCTION

select * from get_data('EUR');

payment_id
rental_id
amount

1
1
116.0000

2
2
232.0000

SELECT 2