Обсуждение: Function inserting into tstzrange ? (syntax error at or near...)

Поиск
Список
Период
Сортировка

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

От
Laura Smith
Дата:
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...


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 !

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

От
"David G. Johnston"
Дата:
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.

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

От
Adrian Klaver
Дата:
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

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

От
Tom Lane
Дата:
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

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

От
Laura Smith
Дата:
>
> 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)');

Lesson learnt !

Thanks again.

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

От
Adrian Klaver
Дата:
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

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

От
Adrian Klaver
Дата:
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

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.