From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>, postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Function inserting into tstzrange ? (syntax error at or near...) |
Date: | 2024-02-18 17:49:36 |
Message-ID: | fda33e69-fc3b-4248-b071-e52a432c4b31@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-02-18 17:52:59 | Re: Function inserting into tstzrange ? (syntax error at or near...) |
Previous Message | David G. Johnston | 2024-02-18 17:48:35 | Re: Function inserting into tstzrange ? (syntax error at or near...) |