Postgres custom function with CASE

Question

I have table watchers:

------------------------------------------
time_type | time_count | timestamp_created
------------------------------------------
  'hour'  |   1        |   2016-12-08 15:56:26.169614
  'hour'  |   13       |   ... 
  'day'   |   5        |   ...

I try to get integer based on time_type and time_count values:

CREATE OR REPLACE FUNCTION af_calculate_range(tt text,tc  integer) RETURNS integer AS $$ 
            SELECT tt, CASE tt WHEN 'day' THEN tc * 60 * 60 
                               WHEN 'hour' THEN tc * 60
                       END
        FROM watchers;

          $$
    LANGUAGE SQL;

SELECT af_calculate_range(time_type, time_count) FROM watchers

but I get an error:

ERROR:  return type mismatch in function declared to return integer  
DETAIL:  Final statement must return exactly one column. 
CONTEXT:  SQL function "af_calculate_range"
********** Error **********

Usage:

SELECT * FROM watchers
WHERE  EXTRACT(EPOCH FROM now()) > (EXTRACT(EPOCH FROM timestamp_created) +
 af_calculate_range(time_type, time_count) )

if time_type = 'hour' and time_count = 1 the output should be 3600 seconds.

What's wrong with my example:

I used https://www.postgresql.org/docs/7.4/static/functions-conditional.html

and https://www.postgresql.org/docs/9.1/static/sql-createfunction.html

Why are you reading a the manual for a completely outdated version (7.4) and a discontinued version (9.1)? — user330315
– user330315, Commented Dec 19, 2016 at 8:15

Tim Biegeleisen · Accepted Answer · 2016-12-18 14:52:00Z

5

A function can only return a single value, so you probably intended to do this:

CREATE OR REPLACE FUNCTION af_calculate_range(tt text, tc integer) RETURNS integer AS $$ 
        SELECT CASE WHEN tt = 'day' THEN tc * 60 * 60    -- return a (single) scalar 
                    WHEN tt = 'hour' THEN tc * 60
               END
        FROM watchers;
      $$
LANGUAGE SQL;

But as @Mureinik pointed out, you don't even need to do a SELECT; just use the CASE expression directly.

edited Dec 18, 2016 at 14:52

answered Dec 18, 2016 at 14:45

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2016-12-18 14:53:08Z

3

You don't need a query there, as you're passing all the values to the function - just return the value of the case:

CREATE OR REPLACE FUNCTION af_calculate_range(tt TEXT, tc INTEGER) 
RETURNS INTEGER IMMUTABLE AS $$
BEGIN
    RETURN CASE tt WHEN 'day' THEN tc * 60 * 60
                   WHEN 'hour' THEN tc * 60
           END;
END;
$$
LANGUAGE PLPGSQL;

edited Dec 18, 2016 at 14:53

answered Dec 18, 2016 at 14:49

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2016-12-19 07:36:31Z

1

You are over complicating. Store an interval in instead

select now(), now() + interval '2 days';
              now              |           ?column?            
-------------------------------+-------------------------------
 2016-12-19 05:23:51.856137-02 | 2016-12-21 05:23:51.856137-02

create table watchers (
    the_interval interval,
    timestamp_created timestamp
);

insert into watchers (the_interval, timestamp_created) values
('1 hour', '2016-12-08 15:56:26.169614');

Now the query will be:

select * 
from watchers
where now() > timestamp_created + the_interval
;
 the_interval |     timestamp_created      
--------------+----------------------------
 01:00:00     | 2016-12-08 15:56:26.169614

edited Dec 19, 2016 at 7:36

answered Dec 18, 2016 at 14:53

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

snaggs Over a year ago

I'm new in sql, how it should look like?

Clodoaldo Neto Over a year ago

@snaggs: Edited

Collectives™ on Stack Overflow

Postgres custom function with CASE

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related