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

On Sunday, February 18, 2024, Laura Smith <
n5d9xq3ti233xiyif2vp@protonmail.ch> 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...

It’s telling you you have a syntax error so no you’ve don’t have the syntax
right.

The [ and ) used to describe the bound inclusiveness of the range must
appear in a string literal, I.e. enclosed in single quotes.

The functional constructors for ranges allow you to specify a single string
literal containing both as the third argument.

David J.

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

Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> writes:

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...

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,'[)')) ...

although I believe '[)' is the default bounds type so that could be
simplified to

VALUES(p_event_id, tstzrange(p_start_time,p_end_time)) ...

regards, tom lane

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)');

Lesson learnt !

Thanks again.

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$

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

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