CODEWITHSUNDEEP
postgresqlpostgresql-9.2psqlbetween
AixNPanes
AixNPanes

Reputation: 1260

Between doesn't yield expected results in pgsql in function

I have the following sequence in pgsql in PostgresSQL 9.2.10. The SELECT statement works as expected.

foreman=> SELECT 'architectures', id, created_at FROM architectures WHERE created_at BETWEEN '2015-05-04 12:03:38.234225' AND now()
;
 ?column? | id | created_at
----------+----+------------
(0 rows)

The following is the function definition:

foreman=> CREATE OR REPLACE FUNCTION getTableIfUpdated(_created_at_text TEXT)
foreman-> RETURNS SETOF tablecreatedat_rs
foreman-> AS
foreman-> $func$
foreman$> DECLARE
foreman$>   created_at TIMESTAMP;
foreman$>   t CHARACTER VARYING;
foreman$>   r RECORD;
foreman$> BEGIN
foreman$>   created_at = to_timestamp(_created_at_text, 'DD/MM/YYYY hh24:mi:ss');
foreman$>   FOR t IN SELECT table_name FROM information_schema.tables t1 WHERE EXISTS (SELECT column_name FROM information_schema.columns t2 WHERE column_name='created_at' AND t1.table_name=t2.table_name) AND EXISTS (SELECT column_name FROM information_schema.columns t3 WHERE column_name='id' AND t1.table_name=t3.table_name) ORDER BY table_name
foreman$>     LOOP
foreman$>       FOR r in EXECUTE 'SELECT $1, id, created_at FROM ' || t || ' WHERE created_at BETWEEN $2 AND now()' USING t, created_at
foreman$>       LOOP 
foreman$>         RETURN NEXT r;
foreman$>       END LOOP;
foreman$>     END LOOP;
foreman$> END                 
foreman$> $func$ LANGUAGE plpgsql;
CREATE FUNCTION

The following is the function invocation:

foreman=> select getTableIfUpdated('2015-05-04 12:03:38.234225');
                         gettableifupdated                      
--------------------------------------------------------------------
 (architectures,2,"2015-05-04 01:34:40.800257")
 (architectures,1,"2015-05-04 01:34:40.796382")
 (audits,1,"2015-05-04 01:34:24.950228")
 (audits,2,"2015-05-04 01:34:25.330426")
 (audits,3,"2015-05-04 01:34:32.385152")

Note that I expect 0 rows from the function. However, the values returned all have created_at which occurs before the specified value. Suggestions?

Upvotes: 0

Views: 68

Answers (1)

alexius
alexius

Reputation: 2576

line

created_at = to_timestamp(_created_at_text, 'DD/MM/YYYY hh24:mi:ss');

should be replaced with

created_at = to_timestamp(_created_at_text, 'YYYY-MM-DD hh24:mi:ss');

because it converts timestamp incorrectly:

select to_timestamp('2015-05-04 12:03:38.234225', 'DD/MM/YYYY hh24:mi:ss');
  to_timestamp
------------------------
0009-11-05 12:03:38+00

Upvotes: 2

Related Questions