On 2/18/24 10:40, Adrian Klaver wrote:
On 2/18/24 10:30, Laura Smith wrote:
There's not bespoke SQL syntax for constructing a range. You must
use a function, something like
VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ...
Thanks all for your swift replies.
Serves me right for assuming I could use variable substitution where
text would normally go, i.e. I thought I could just mimic the below
example from the docs by substituting the variables:
INSERT INTO reservation VALUES
(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
Yeah, a quick and dirty example:
\d event_sessions
Table "public.event_sessions"
Column | Type | Collation | Nullable |
Default
-------------------+--------------------------+-----------+----------+---------
event_id | text | | not null |
evt_sess_id | text | | not null |
evt_sess_times | tstzrange | | not null |
evt_sess_inserted | timestamp with time zone | | not null |
now()
Indexes:
"event_sessions_pkey" PRIMARY KEY, btree (evt_sess_id)
CREATE OR REPLACE FUNCTION public.new_event_session(p_event_id text,
p_start_time timestamp with time zone, p_end_time timestamp with time
zone, p_sess_title text, p_sess_desc text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_session_id text;
BEGIN
EXECUTE format('INSERT INTO event_sessions(event_id, evt_sess_id,
evt_sess_times)
VALUES($1, 2, tstzrange($2, $3)) RETURNING evt_sess_id') INTO
v_session_id
USING p_event_id, p_start_time, p_end_time;
RETURN v_session_id;
END;
$function$
I over complicated the above, it can be simplified to:
CREATE OR REPLACE FUNCTION public.new_event_session(p_event_id text,
p_start_time timestamp with time zone, p_end_time timestamp with time
zone, p_sess_title text, p_sess_desc text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_session_id text;
BEGIN
INSERT INTO event_sessions(event_id, evt_sess_id, evt_sess_times)
VALUES(p_event_id, 2, tstzrange(p_start_time, p_end_time))
RETURNING evt_sess_id INTO v_session_id;
RETURN v_session_id;
END;
$function$
select new_event_session('1', '2024-02-18', '2024-02-20', 'test', 'test
desc');
new_event_session
-------------------
2
select * from event_sessions ;
event_id | evt_sess_id | evt_sess_times
| evt_sess_inserted
----------+-------------+-----------------------------------------------------+-------------------------------
1 | 2 | ["2024-02-18 00:00:00-08","2024-02-20
00:00:00-08") | 2024-02-18 10:47:40.671922-08
Lesson learnt !
Thanks again.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
