Re: Function inserting into tstzrange ? (syntax error at or near...)

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
justmimic 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$


select new_event_session('1', '2024-02-18', '2024-02-20', 'test', 'test 
desc');
  new_event_session
-------------------
  2

> 
> Lesson learnt !
> 
> Thanks again.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com