asked on

function in postgres is not giving me return.

1. i've created the following function in postgres.

CREATE OR REPLACE FUNCTION get_level_between_dates(in_container_id integer, "start" time with time
zone, "end" time with time zone) RETURNS SETOF integer AS
'
SELECT level FROM level
CREATE OR REPLACE FUNCTION get_level_between_dates(in_container_id integer, "start" time with time
zone, "end" time with time zone) RETURNS SETOF integer AS

'SELECT level FROM level
WHERE container_id = $1
AND date >= $2 AND date <= $3 '
LANGUAGE SQL;

2. when i call the function with the following parameters (a) i dont receive anything as response, when i pass the query as in (b) i receive response, what is my mistake?

a)
select get_level_between_dates(1048577, '2007-04-23 08:00:00', '2007-07-22 08:00:00');

b)
select level from level where container_id = 1048577 and date >= '2007-04-23 08:00:00'
and date <= '2007-07-22 08:00:00'

Adrian Pop

Hi,

This is extreamly strange. It should work.

Try using EXPLAIN in the function select to see what's happening.
'EXPLAIN SELECT level FROM level
WHERE container_id = $1
AND date >= $2 AND date <= $3 '

What type does level.level has?

Cheers,
za-k/

Erwin Pombett

ASKER

i've put Explain in front of my query(i added 2 to the name): here is the error returned:

psql:get_level_between_dates2.sql:10: ERROR: return type mismatch in function declared to return integer
DETAIL: Function's final statement must be a SELECT.
CONTEXT: SQL function "get_level_between_dates2"

here's my table description :
CREATE TABLE "level"
(
container_id integer,
date timestamp without time zone,
"level" integer,
sensor_nbr integer
)

i changed the return type to text, but still have same error.

is it possible to have a translation to plpgsql ?
i'm just beginning with functions in postgres, and this first SQL function doesn't reconfort.
thanks in advance.

SOLUTION

Adrian Pop

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Erwin Pombett

ASKER

here's my function

CREATE OR REPLACE FUNCTION get_level_between_dates(in_container_id integer, "start" time with time
zone, "end" time with time zone) RETURNS SETOF integer AS
'SELECT level FROM level
WHERE container_id = $1
AND date >= $2 AND date <= $3 '
LANGUAGE SQL;

call 1:
SELECT * from get_level_between_dates(1048577, '2007-01-01 00:00:00', '2007-03-31 18:00:00');
no answer
call 2:
SELECT * from get_level_between_dates(1048577, '2007-01-01 00:00:00', '2007-03-31 22:00:00');
21577 rows - >looks right
call 3:
SELECT * from get_level_between_dates(1048577, '2007-03-01 00:00:00', '2007-03-31 22:00:00');
21577 rows -> looks strange. should be less than the first interval (call 2),

now, i've try the always ecologistic earthman2 function which return what i want,
amazin earthman why is the first function returning all the rows without filtering by the date?

btw thanks a lot!!!!!

note: adrpo, the double function header was a typo error.

earth man2

you need to realize the difference between time and timestamp data types.

ie time does not have a date component. Also beware of using column names that are SQL keywords, ie DATE ( and level ) are keywords to avoid in object names.
So level.date explicitly denotes a table column.