Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbmEW-00GwGi-L4 for pgsql-general@arkaria.postgresql.org; Sun, 18 Feb 2024 18:49:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rbmEU-00AwUq-HT for pgsql-general@arkaria.postgresql.org; Sun, 18 Feb 2024 18:49:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbmEU-00AwUe-6D for pgsql-general@lists.postgresql.org; Sun, 18 Feb 2024 18:49:46 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbmEQ-007P82-5Z for pgsql-general@lists.postgresql.org; Sun, 18 Feb 2024 18:49:44 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 4AC105C006E; Sun, 18 Feb 2024 13:49:41 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Sun, 18 Feb 2024 13:49:41 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708282181; x=1708368581; bh=/Aek7F/yy6I/FfMg/xbbe2RtAeegKCUcru/JkVSI/6k=; b= aE6PVGgd7P21J+6ACQl3GBMewMgrHM7SKVwOY/1vjj8SsF3SLk0RMsVY3sHLZdA9 9vPfnHeAKe6IGNljWqlMR4+2vWKzHeCJgN/RyfBxEOCTk/1qyrP0+ue3AF14QHie mRPrScRsc/1svTUZUP7dx9UJ+GH6pV0TlrPKeu1u4mnkNiZTNJnrxJNOxOjrIk+4 W0flKaDdRJsBxcEOqacuzazVojFYm3xeFQxIvxO2eniypt0tLGRxKkipDuVjuf5o AhSNDyfTGqB5r6guZ9+jgbeyy8PsygFTReM0l6GRK71F6A4+lhFs8m/yH3zlzPM6 W23KHq/PLO5SXQoq+TVbyA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708282181; x= 1708368581; bh=/Aek7F/yy6I/FfMg/xbbe2RtAeegKCUcru/JkVSI/6k=; b=f X5XtZt2hE6MvauS0D9KAn8sdzv8QidwcwXdHsN4JdgpVr9phv+gfAcUdv+ei2Cfe HDqpgKkIG8G8PbimnUCoUPZZ/zmqSNguAFuI4AoVCyoE4nZ5zqRTnROcnvjUDF6x DY5bO82LnjNQcIAKIaSu7LUElc+YR3r8RmJcQVD3M7ZKGNJ2/SjSQZ0xtks6d4Hc PfyE5h5guaWUuieqAaG69/c+ziqIddmg0Rq6/z7qpcMmQgiC28pS1oSdWwEv3ce9 igrtCgB8D2E+ZX75GtGkBimmKYAKf1m70XKNWCfMg8V/DkO+xg0bTcib/rHJPG7J gczCaubRSD6gE4hr/Vkdw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrvdeigdduvddtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuhffvfhgjtgfgsehtke ertddtvdejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghruceorggurhhirghnrdhk lhgrvhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrthhtvghrnhephfduveeufe evffejieegheffteetheejveettdfgtdefvdegjefgvedvfefhteffnecuvehluhhsthgv rhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvh gvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 18 Feb 2024 13:49:40 -0500 (EST) Message-ID: <884593a7-07bb-4fcf-90b2-d19029748a06@aklaver.com> Date: Sun, 18 Feb 2024 10:49:39 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Function inserting into tstzrange ? (syntax error at or near...) Content-Language: en-US From: Adrian Klaver To: Laura Smith , postgre 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> In-Reply-To: <1a7f0c3f-f241-4e5c-a38b-7468ccedcdff@aklaver.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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