How can I create a subquery or function

You can't create a function including a SELECT.
UDx-s, written in C++, Python, Java or R, don't allow cursors as of yet.
And you can't CREATE OR REPLACE FUNCTION .... BEGIN ... SELECT .. RETURN .. END; - You can't put a SELECT into the body of a SQL based function.
Do you need help to create a fullselect returning COUNT(*) of the days in a calendar that are holidays? That would be the workaround. Store that somewhere central, and copy-paste it into all queries needing it - as a Common Table Expression.

Hi,
You can try with Analytic queries and cross join to get the results.
create local temp table calendar_date ("date" date, holiday int) on commit preserve rows;
create local temp table item(id int,start_date date, end_date date) on commit preserve rows;

INSERT INTO calendar_date values('2019-10-01',0);
INSERT INTO calendar_date values('2019-10-02',0);
INSERT INTO calendar_date values('2019-10-03',0);
INSERT INTO calendar_date values('2019-10-04',0);
INSERT INTO calendar_date values('2019-10-05',0);
INSERT INTO calendar_date values('2019-10-06',0);
INSERT INTO calendar_date values('2019-10-07',0);
INSERT INTO calendar_date values('2019-10-08',0);
INSERT INTO calendar_date values('2019-10-09',1);
INSERT INTO calendar_date values('2019-10-10',0);
INSERT INTO calendar_date values('2019-10-11',0);
INSERT INTO calendar_date values('2019-10-12',0);
INSERT INTO calendar_date values('2019-10-13',0);
INSERT INTO calendar_date values('2019-10-14',0);
INSERT INTO calendar_date values('2019-10-15',0);
INSERT INTO calendar_date values('2019-10-16',0);
INSERT INTO calendar_date values('2019-10-17',0);
INSERT INTO calendar_date values('2019-10-18',1);
INSERT INTO calendar_date values('2019-10-19',0);
INSERT INTO calendar_date values('2019-10-20',0);
INSERT INTO calendar_date values('2019-10-21',0);
INSERT INTO calendar_date values('2019-10-22',0);
INSERT INTO calendar_date values('2019-10-23',0);
INSERT INTO calendar_date values('2019-10-24',0);
INSERT INTO calendar_date values('2019-10-25',0);
INSERT INTO calendar_date values('2019-10-26',0);
INSERT INTO calendar_date values('2019-10-27',1);
INSERT INTO calendar_date values('2019-10-28',0);
INSERT INTO calendar_date values('2019-10-29',0);
INSERT INTO calendar_date values('2019-10-30',0);
INSERT INTO calendar_date values('2019-10-31',0);
INSERT INTO calendar_date values('2019-11-01',0);
INSERT INTO calendar_date values('2019-11-02',0);
INSERT INTO calendar_date values('2019-11-03',0);
INSERT INTO calendar_date values('2019-11-04',0);
INSERT INTO calendar_date values('2019-11-05',0);
INSERT INTO calendar_date values('2019-11-06',0);
INSERT INTO calendar_date values('2019-11-07',0);
INSERT INTO calendar_date values('2019-11-08',0);
INSERT INTO calendar_date values('2019-11-09',1);
INSERT INTO calendar_date values('2019-11-10',0);
INSERT INTO calendar_date values('2019-11-11',0);
INSERT INTO calendar_date values('2019-11-12',0);
INSERT INTO calendar_date values('2019-11-13',0);
INSERT INTO calendar_date values('2019-11-14',0);
INSERT INTO calendar_date values('2019-11-15',0);
INSERT INTO calendar_date values('2019-11-16',0);
INSERT INTO calendar_date values('2019-11-17',0);
INSERT INTO calendar_date values('2019-11-18',1);
INSERT INTO calendar_date values('2019-11-19',0);
INSERT INTO calendar_date values('2019-11-20',0);
INSERT INTO calendar_date values('2019-11-21',0);
INSERT INTO calendar_date values('2019-11-22',0);
INSERT INTO calendar_date values('2019-11-23',0);
INSERT INTO calendar_date values('2019-11-24',0);
INSERT INTO calendar_date values('2019-11-25',0);
INSERT INTO calendar_date values('2019-11-26',0);
INSERT INTO calendar_date values('2019-11-27',1);
INSERT INTO calendar_date values('2019-11-28',0);
INSERT INTO calendar_date values('2019-11-29',0);
INSERT INTO calendar_date values('2019-11-30',0);

INSERT INTO item
values (1,'2019-10-01','2019-10-20');

INSERT INTO item
values (2,'2019-10-01','2019-10-31');

INSERT INTO item
values (3,'2019-10-01','2019-10-03');

INSERT INTO item
values (4,'2019-10-01','2019-11-30');

INSERT INTO item
values (5,'2019-10-01','2019-11-10');

select distinct id,start_date,end_date,holiday from
(
select id,start_date,end_date,sum(holiday) over(partition by id) as holiday from item cross join calendar_date
where end_date >= date
order by 1,2
)A;

performance wise which is 2023 calendar better subquery or scalar function ?

Classic aggregate query is also to consider:

  select i.id
       , i.start_date
       , i.end_date
       , sum(c.holiday) 
    from item          as i
    join calendar_date as c  on c."date" >= i.start_date
                            and c."date" <= i.end_date
group by i.id
       , i.start_date
       , i.end_date
order by i.id asc;

There is room for optimization if the number of couple (start_date, end_date) has lots of duplicates.

Oh, just noticed it's an old topic.