Madhu.Lanka
2012-07-20 02:33:36 UTC
Hi Friends
I am creating the function like
CREATE OR REPLACE FUNCTION getrowstest3(pname character varying,ppassword
character varying)
RETURNS SETOF getrows AS
$BODY$
declare
r getrows;
begin
for r in EXECUTE
'select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.rema
rks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_na
me,p.contact_person,
p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,
p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.r
ight_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.ri
ght_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.
right_user_management,
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.d
elete,r.query,r.lock from ksdi.user_roles u,ksdi.principals p,ksdi.roles r
where p.PRINCIPAL_ID = u.USER_ID and
r.role_id = u.role_id and p.name =$1 and p.password = $2;'
loop
return next r;
end loop;
return;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Where getrows is the type created by me;
It is created successfully.
I am trying to call the function I pgadmin with the following command
select * from getrowstest2('general_user','***@123');
I am getting the following error
ERROR: there is no parameter $1
LINE 5: r.role_id = u.role_id and p.name= $1 and p.password = $2
^
QUERY: select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.rema
rks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_na
me,p.contact_person,
p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,
p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.r
ight_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.ri
ght_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.
right_user_management,
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.d
elete,r.query,r.lock from ksdi.user_roles u,ksdi.principals p,ksdi.roles r
where p.PRINCIPAL_ID = u.USER_ID and
r.role_id = u.role_id and p.name= $1 and p.password = $2
CONTEXT: PL/pgSQL function "getrowstest2" line 8 at FOR over EXECUTE
statement
********** Error **********
ERROR: there is no parameter $1
SQL state: 42P02
Context: PL/pgSQL function "getrowstest2" line 8 at FOR over EXECUTE
statement
Can anyone please help me to resolve the issue.
Thanks in Advance
Regards
Madhu.Lanka
I am creating the function like
CREATE OR REPLACE FUNCTION getrowstest3(pname character varying,ppassword
character varying)
RETURNS SETOF getrows AS
$BODY$
declare
r getrows;
begin
for r in EXECUTE
'select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.rema
rks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_na
me,p.contact_person,
p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,
p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.r
ight_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.ri
ght_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.
right_user_management,
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.d
elete,r.query,r.lock from ksdi.user_roles u,ksdi.principals p,ksdi.roles r
where p.PRINCIPAL_ID = u.USER_ID and
r.role_id = u.role_id and p.name =$1 and p.password = $2;'
loop
return next r;
end loop;
return;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Where getrows is the type created by me;
It is created successfully.
I am trying to call the function I pgadmin with the following command
select * from getrowstest2('general_user','***@123');
I am getting the following error
ERROR: there is no parameter $1
LINE 5: r.role_id = u.role_id and p.name= $1 and p.password = $2
^
QUERY: select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.rema
rks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_na
me,p.contact_person,
p.role_group,p.role_description,p.first_name,p.last_name,p.pincode,p.status,
p.department_id,p.designation_id,r.role_name,r.right_edit,r.right_filter,r.r
ight_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.ri
ght_vector_download,r.right_wps,r.right_queries_filter,r.right_google_map,r.
right_user_management,
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.d
elete,r.query,r.lock from ksdi.user_roles u,ksdi.principals p,ksdi.roles r
where p.PRINCIPAL_ID = u.USER_ID and
r.role_id = u.role_id and p.name= $1 and p.password = $2
CONTEXT: PL/pgSQL function "getrowstest2" line 8 at FOR over EXECUTE
statement
********** Error **********
ERROR: there is no parameter $1
SQL state: 42P02
Context: PL/pgSQL function "getrowstest2" line 8 at FOR over EXECUTE
statement
Can anyone please help me to resolve the issue.
Thanks in Advance
Regards
Madhu.Lanka