passing parameters to plpgsql function problem

[Vovin]

Hi,

I'm having a problem passing a comma-delimited list of values to a plpgsql function (stored procedure) that I've created. I'm calling the function as follows:

select * from get_total_jobs_passed_at_eol(268, date('2006-03-19'), 2, '1,2', '1');

The 4th parameter is the one that I'm having trouble with (i.e. '1,2'). The problem is that the line "user_session.user_role IN (userRole)" does not seem to be able to pick up the numbers that I'm passing in correctly (it works ok if I only pass in 1 single number - any more than that and it just returns 0). Is there a correct way of doing this? Should I be using a different datatype rather than text for the parameter? Any help would be much appreciated. Thanks. A (simplified) copy of the function is shown below:

-- Function: get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

-- DROP FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

CREATE OR REPLACE FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)
RETURNS integer AS
$BODY$

DECLARE
userId bigint;
weekEndParam date;
numOfWeeks integer;
userRole text;
workFlowSystemId text;

totalJobsPassedAtEol integer;

BEGIN
userId := $1;
weekEndParam := $2;
numOfWeeks := $3;
userRole := $4;
workFlowSystemId := $5;

RAISE LOG '******** userRole is: %', userRole;
RAISE LOG '******** workFlowSystemId is: %', workFlowSystemId;

select into totalJobsPassedAtEol
count(distinct job_history.job_id) as totalEolPasses
from
job_history,
sm_user,
user_session
where
sm_user.sm_user_id=user_session.user_id and
sm_user.sm_user_id=userId and
job_history.session_id=user_session.session_id and
user_session.user_role IN (userRole);

RETURN totalJobsPassedAtEol;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text) OWNER TO single;

 

[ceco]

if there are not very much elements you can use array of integers

the values is
CREATE FUNCTION ... (...., userRole integer[], ...) AS

and the usage will be

WHERE .... AND user_session.user_role ANY (userRole)

I didn't test the correct syntax, but I think it is like that

 

[ceco]

you call the function like that

SELECT .... (...., '{1,2', ...)

 

[Vovin]

thanks for your help but unfortunately I didn't manage to solve the problem using your solutions. I think the correct way to do it is by using EXECUTE but currently this isn't returning the value that I'm expecting (it always returns 0). If you have any ideas why this might be so then any help would be much appreciated. My code is below:

-- Function: get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

-- DROP FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

CREATE OR REPLACE FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)
RETURNS integer AS
$BODY$

DECLARE
userId bigint;
weekEndParam date;
numOfWeeks integer;
userRole text;
workFlowSystemId text;

totalJobsPassedAtEol record;

BEGIN
userId := $1;
weekEndParam := $2;
numOfWeeks := $3;
userRole := $4;
workFlowSystemId := $5;


FOR totalJobsPassedAtEol IN Execute 'select count(distinct job_history.job_id) as tot
from
job_history,
sm_user,
user_session
where
sm_user.sm_user_id=user_session.user_id and
sm_user.sm_user_id= ' || userId || ' and
job_history.session_id=user_session.session_id and
user_session.user_role IN (' || userRole || ') and
user_session.workflowsystem_id=3

and job_history.event_occured <= ' || weekEndParam || ' + 1
and job_history.event_occured >= ' || weekEndParam || ' - (' || numOfWeeks || ' * 7) + 1
and job_history.job_id IN (select
distinct job_history.job_id
from
job_history
where
job_history.job_event_code=9 and
job_history.event_occured >= ' || weekEndParam || ' - (' || numOfWeeks || ' * 7) + 1
)'
LOOP
RETURN totalJobsPassedAtEol.tot;
END LOOP;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text) OWNER TO single;

 

[Vovin]

The above function does actually solve the problem of passing a comma-delimited list of values to a plpgsql function.

If I remove everything that refers to a date then this seems to work ok. For some reason when I pass a date into the sored proc it doesn't treat it correctly. If I use the now() function within my stored proc this works ok but is not what I want. Does anyone have any ideas on how to pass dates into a stored proc successfully. The example of using the now() function is displayed below:

-- Function: get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

-- DROP FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)

CREATE OR REPLACE FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text)
RETURNS integer AS
$BODY$

DECLARE
userId bigint;
weekEndParam date;
numOfWeeks integer;
userRole text;
workFlowSystemId text;

totalJobsPassedAtEol record;

BEGIN
userId := $1;
weekEndParam := $2;
numOfWeeks := $3;
userRole := $4;
workFlowSystemId := $5;

FOR totalJobsPassedAtEol IN Execute 'select count(distinct job_history.job_id) as tot
from
job_history,
sm_user,
user_session
where
sm_user.sm_user_id=user_session.user_id and
sm_user.sm_user_id= ' || userId || ' and
job_history.session_id=user_session.session_id and
user_session.user_role IN (' || userRole || ') and
user_session.workflowsystem_id=3
and job_history.event_occured <= now() '
LOOP
RETURN totalJobsPassedAtEol.tot;
END LOOP;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_Total_Jobs_Passed_At_Eol(int8, date, int4, text, text) OWNER TO single;

 

[gwinn7]

What about changing the "date" to a "text" type? If there is no date, you can COALESCE, or CASE your date value and pass it as a string to the function.

Question...

Just a small thing, but as for your parameter assignments, do you think it would be better to use the ALIAS statement? Like...

DECLARE
userId ALIAS FOR $1;

etc...

OR, embed your variable names with your parameter declarations?

???

For peak performance, I was interested why you would use a variable assignment during run-time and not make it more declarative. Yes, I know its a very small thing. I was just curious.

Gary
gwinn7