0

I'm trying to make a reservation function for a computed field in Hasura (PostgreSQL). The code I'm trying to make gets the game_length from the table rooms and returns a timestamp with the game_length + start_time. However, I am new to functions in SQL and I can't seem to get this function working, as I am getting an error that states: postgres-error: operator does not exist: bigint = text. Does anyone know what I am doing wrong?

The test table consists of: id (int), start_time (timestamp), game_length(int), and room_id(int)

CREATE OR REPLACE FUNCTION public.calculate_reservation_total_time(t test)
    RETURNS timestamp with time zone
    LANGUAGE sql
    STABLE
AS $function$  
    SELECT t.start_time + (SELECT count(*) FROM rooms WHERE id=t.room_id ||' minutes')::interval; 
$function$
Logan Rios
  • 87
  • 10
  • Wouldn't that be `FROM t` or if you use `rooms` then `rooms.room_id`. Not really sure what `t rooms` is there for anyway? – Adrian Klaver Jun 22 '21 at 15:12
  • Please add the definition of your type ```test```. My guess is ```rooms.id``` is of type ```bigint``` while ```t.room_id``` is of type ```text```. So you would need to convert one to the other before comparing them. – Islingre Jun 22 '21 at 16:16
  • @Islingre got it! it doesent seem that way, there both integer – Logan Rios Jun 22 '21 at 16:44

1 Answers1

1

You have an error in your parantheses:

(SELECT count(*) FROM rooms WHERE id=t.room_id ||' minutes')::interval

This will concatenate t.room_id with ' minutes' (thus you get something of type text) and then compares this with id of rooms.

Try following statement:

CREATE OR REPLACE FUNCTION public.calculate_reservation_total_time(t test)
    RETURNS timestamp with time zone
    LANGUAGE sql
    STABLE
AS $function$  
    SELECT t.start_time +
        ((SELECT count(*) FROM rooms WHERE id=t.room_id) ||' minutes')::interval; 
$function$
Islingre
  • 2,030
  • 6
  • 18