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

On 2/18/24 09:40, Laura Smith wrote:
> I'm sure I'm doing something stupid here, but I think I've got the syntax right ?
> 
> The error I'm seeing:
> psql:event_session_funcs.sql:26: ERROR:  syntax error at or near "["
> LINE 11:         VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI...

Two ways to build a range:


select '[2024-02-18, 2024-02-20)'::tstzrange;
                       tstzrange
-----------------------------------------------------
  ["2024-02-18 00:00:00-08","2024-02-20 00:00:00-08")

or

select tstzrange('2024-02-18', '2024-02-20', '[)');
                       tstzrange
-----------------------------------------------------
  ["2024-02-18 00:00:00-08","2024-02-20 00:00:00-08")


See here:

https://www.postgresql.org/docs/current/rangetypes.html

for more information.

> 
> 
> The function:
> CREATE OR REPLACE FUNCTION new_event_session(p_event_id text,    p_start_time timestamptz,
>      p_end_time timestamptz,
>      p_sess_title text,
>      p_sess_desc text
>      ) RETURNS text AS $$
> DECLARE
> v_session_id text;
> BEGIN
>      INSERT INTO event_sessions(event_id,evt_sess_times)
>          VALUES(p_event_id,[p_start_time,p_end_time)) RETURNING evt_sess_id INTO v_session_id;
> // REST OF FUNCTION REMOVED FOR BREVITY
> 
> 
> 
> The table definition:
> CREATE TABLE IF NOT EXISTS event_sessions (    event_id text NOT NULL,
>      evt_sess_id text NOT NULL PRIMARY KEY DEFAULT ksuid_pgcrypto_micros(),
>      evt_sess_times tstzrange NOT NULL,
>      evt_sess_inserted timestamptz not null default now(),
>      CONSTRAINT fk_evt_id
>      FOREIGN KEY(event_id)
>      REFERENCES events(event_id),
>      EXCLUDE USING gist (
>          event_id WITH =,
>          evt_sess_times WITH &&
>      )
> );
> 
> 
> N.B. I'm calling from Go, the library does not natively support tstzrange, hence the need to break-out the input
parameters.
> 
> Thanks !
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com