pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>
To: postgre <pgsql-general@lists.postgresql.org>
Subject: Re: Function inserting into tstzrange ? (syntax error at or near...)
Date: Sun, 18 Feb 2024 10:49:39 -0800
Message-ID: <884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com> (raw)
In-Reply-To: <1a7f0c3f-f241-4e5c-a38b-7468ccedcdff@aklaver.com>
References: <5Tfn0cxmVyZ3uSyl4_GqlisTneXWCuH0I5Jk6PgEGtFXkBGlTXiXfT8ffk1YyF1zhGoGOxdpmpOPvOQfK4l74T4fK8xKMxTHcs-J_08M8j4=@protonmail.ch>
	<3883445.1708278779@sss.pgh.pa.us>
	<2nqXMmBAg_UsRbjwNzuT1pU8YZwfkxPEdD-dTlTloMylGJP_99629l9bZEpUrXIoOM-SmnvWNk9fCwBMq7-5zk1au04wAh790LINW36_Zmc=@protonmail.ch>
	<1a7f0c3f-f241-4e5c-a38b-7468ccedcdff@aklaver.com>

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@aklaver.com






view thread (7+ messages)

Message-ID: <884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com>
Permalink:  ../884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com/
Also on:    postgresql.org/message-id/884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, n5d9xq3ti233xiyif2vp@protonmail.ch
  Subject: Re: Function inserting into tstzrange ? (syntax error at or near...)
  In-Reply-To: <884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com>
  References: <5Tfn0cxmVyZ3uSyl4_GqlisTneXWCuH0I5Jk6PgEGtFXkBGlTXiXfT8ffk1YyF1zhGoGOxdpmpOPvOQfK4l74T4fK8xKMxTHcs-J_08M8j4=@protonmail.ch> <3883445.1708278779@sss.pgh.pa.us> <2nqXMmBAg_UsRbjwNzuT1pU8YZwfkxPEdD-dTlTloMylGJP_99629l9bZEpUrXIoOM-SmnvWNk9fCwBMq7-5zk1au04wAh790LINW36_Zmc=@protonmail.ch> <1a7f0c3f-f241-4e5c-a38b-7468ccedcdff@aklaver.com> <884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox