query inside function returns NULL, query returns a value

Question

Disclaimer: I am new to PostgreSQL, and even newer to functions in plpgSQL. I've read the documentation but I am stumped.

Anyway, I have this function, to pick a charge band out of a table for asterisk CDR rating:

CREATE FUNCTION getBand(
    callDateTime varchar
) RETURNS varchar AS $$
/* Select correct charging band */
DECLARE
    callTime varchar;
    callDay varchar;
    theBand varchar;
BEGIN
    /* Find the time and the day of the call */
    callTime := to_char(to_timestamp(callDateTime,'YYYY-MM-DD HH24:MI:SS'),'HH24:MI:SS');
    callDay := to_char(to_timestamp(callDateTime,'YYYY-MM-DD HH24:MI:SS'),'Day');
    theBand := band 
    FROM bands 
    WHERE day = callDay 
    AND start < callTime 
    AND finish > callTime 
    LIMIT 1;
    RETURN theBand;
END;
$$ LANGUAGE plpgsql

The input string is lifted from the asterisk CDR table, and is in the format 'YYYY-MM-DD HH24:MI:SS'.

The bands table is in the format

day | band | start | finish
============================

Where 'day' is the name of a weekday, band is the charge band for a call ("Peak", "Off Peak", "Weekend") and start and finish are the start and finish times for that charge band.

When I run

SELECT getBand('2013-05-03 11:30:00');

I get a NULL result.

When I run

SELECT band FROM bands
WHERE day = 'Friday' AND start < '11:30:00' and finish > '11:30:00' LIMIT 1;

I get the correct answer (for my data): 'Peak'

I'm confused. Anyone want to tell me where I'm going wrong?

Answer 1

Because to_char(..., 'Day') returns a string that has space-padding at the end. Wrap the expression assigned to callDay in trim():

callDay := trim(to_char(to_timestamp(callDateTime,'YYYY-MM-DD HH24:MI:SS'),'Day'));

(I finally found this by adding raise info 'calltime="%", callday="%"', calltime, callday although I went down quite a few dead ends first)

(This was rather confusing: theBand := band FROM bands ... - it would be more usual to write SELECT band INTO theBand FROM bands ...)

Answer 2

CREATE FUNCTION get_band(call_ts timestamp)
  RETURNS text AS
$func$
   SELECT band 
   FROM   bands 
   WHERE  day = to_char(call_ts,'FMDay')
   AND    start <= call_ts::time
   AND    finish > call_ts::time
   ORDER  BY start 
   LIMIT  1
$func$ LANGUAGE sql STABLE;

• The template pattern 'Day' of to_char() gives you, I quote:

  full capitalized day name (blank-padded to 9 chars)

  To remove the blank padding there is a template pattern modifier: FM. So make that: 'FMDay'. No need for trim().

• YYYY-MM-DD HH24:MI:SS is the standard ISO 8601 format, which works with any locale. There is no good reason to use a varchar, make it a timestamp to begin with. Yes, the data type for a point in time is called timestamp in Postgres (not datetime). That's why I chose the parameter name call_ts for it.

• Don't use unquoted, mixed-case identifiers. Ever. It just leads to confusion. They are all cast to lower case. That's the next reason I chose call_ts.

• Don't use to_char() to get a time from a timestamp. Just cast. Much simpler and faster. call_ts::time

• You don't need plpgsql for this simple query at all, make it a plain SQL function or just use the query.

• In plpgsql it's best to let SQL commands to the work, instead of lots of little assignments (which are comparatively expensive). Even if I had not converted it to LANGUAGE sql, I would use this single compact query.

• LIMIT 1 without ORDER BY gives you an arbitrary result that can change at any time. If you care at all which row you get or if you want it to be stable at least, you have add ORDER BY!

• It's a dubious practice to have a time interval with start < x AND finish > x. Usually you want to include the lower bound, so I used <=.

• Did you consider time zones?

• I would save an integer for the weekday instead of the name of the day.

Answer 3

I'm surprised that either query is working, if day contains '2013-05-03' rather than 'Friday'... There's no issue with your SQL, but there certainly seems to be one with your schema.

Postgres has a range type, and that's probably what you should be using here:

create table bands (
  id serial primary key,
  name varchar,
  playing tsrange
);

insert into bands (name, playing) values ('test', '[2013-05-03 11:00:00, 2013-05-03 12:00:00]');

select name from bands where playing @> '2013-05-03 11:30:00'::timestamp;

create or replace function getBand(_datetime timestamp) returns varchar as $$
  select name from bands where playing @> _datetime limit 1;
$$ language sql;

select getBand('2013-05-03 11:30:00');