On 7/19/2012 10:33 PM, Madhu.Lanka wrote:
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.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,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.right_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.right_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.delete,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','aipl@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.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,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.right_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.right_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.delete,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
You could shorten this right up and avoid the overhead of plpgsql by making it
a pure SQL function without named parameters: (change VOLATILE to STABLE unless
you are actually changing something by selecting on those tables)
CREATE OR REPLACE FUNCTION getrowstest3(character varying, character varying)
RETURNS SETOF getrows AS
$BODY$
select
u.role_id,u.user_id,p.name,p.creation_date,p.telephone_number,p.email,p.remarks,p.city,p.state,p.country,p.postal_address,p.company_name,p.department_name,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.right_search,r.right_browse,
r.right_print,r.right_download,r.right_admin_user,r.right_image_creator,r.right_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.delete,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
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
