PostgreSQL stored procedure data parameter

Question

I have the following stored procedure, which returns 0 results but if the run the query by itself it result lot of results. What am i missing.

CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date) RETURNS int AS $$
DECLARE
    qty int;
BEGIN
    SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;
    RETURN qty;
END;
$$ LANGUAGE plpgsql;

--Execute the function
SELECT countStatistics('2015-01-01 01:00:00') as qty;

return 0 results

SELECT COUNT(*) FROM statistics WHERE time_stamp = '2015-01-01 01:00:00';

return 100+ results

mu is too short · Accepted Answer · 2011-05-13 19:27:29Z

8

You're declaring your baselineDate parameter as a date:

CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date)

but using it as a timestamp:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;

You're getting an implicit cast so countStatistics('2015-01-01 01:00:00') will actually execute this SQL:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01';

and, after the date is implicitly cast back to a timestamp, it will effectively be this:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01 00:00:00';

Try changing your function declaration to use a timestamp:

CREATE OR REPLACE FUNCTION countStatistics(baselineDate timestamp)

answered May 13, 2011 at 19:27

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL stored procedure data parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related