CODEWITHSUNDEEP
sqlpostgresqlcaseplpgsql
snaggs
snaggs

Reputation: 5713

Postgres custom function with CASE

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

Upvotes: 3

Views: 3696

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 312289

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;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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.

Upvotes: 5

Related Questions