Re: ERROR: there is no parameter $1 - Mailing list pgsql-admin

<p><font face="sans-serif" size="2">Hi </font><br /><br /><tt><font size="2">pgsql-admin-owner@postgresql.org wrote on
20/07/201203:33:36:<br /><br />> From: "Madhu.Lanka" <mlanka@avineonindia.com></font></tt><br /><tt><font
size="2">>To: <pgsql-admin@postgresql.org>, </font></tt><br /><tt><font size="2">> Date: 20/07/2012
06:37</font></tt><br/><tt><font size="2">> Subject: [ADMIN] ERROR:  there is no parameter $1</font></tt><br
/><tt><fontsize="2">> Sent by: pgsql-admin-owner@postgresql.org</font></tt><br /><tt><font size="2">> <br />>
HiFriends</font></tt><br /><tt><font size="2">>  </font></tt><br /><tt><font size="2">> I am creating the
functionlike </font></tt><br /><tt><font size="2">>  </font></tt><br /><tt><font size="2">> CREATE OR REPLACE
FUNCTIONgetrowstest3(pname character <br />> varying,ppassword character varying)</font></tt><br /><tt><font
size="2">>  RETURNS SETOF getrows AS</font></tt><br /><tt><font size="2">> $BODY$</font></tt><br /><tt><font
size="2">>declare</font></tt><br /><tt><font size="2">> r getrows;</font></tt><br /><tt><font size="2">>
begin</font></tt><br/><tt><font size="2">> for r in EXECUTE </font></tt><br /><tt><font size="2">> 'select <br
/>>
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,</font></tt><br
/><tt><fontsize="2">>
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,</font></tt><br
/><tt><fontsize="2">>
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,</font></tt><br
/><tt><fontsize="2">>
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock<br/>> from
ksdi.user_rolesu,ksdi.principals p,ksdi.roles r where <br />> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br
/><tt><fontsize="2">> r.role_id = u.role_id and p.name =$1  and p.password = $2;'</font></tt><br /><tt><font
size="2">>loop</font></tt><br /><tt><font size="2">> return next r;</font></tt><br /><tt><font size="2">> end
loop;</font></tt><br/><tt><font size="2">> return;</font></tt><br /><tt><font size="2">> end</font></tt><br
/><tt><fontsize="2">> $BODY$</font></tt><br /><tt><font size="2">>   LANGUAGE plpgsql VOLATILE</font></tt><br
/><tt><fontsize="2">>   COST 100</font></tt><br /><tt><font size="2">>   ROWS 1000;</font></tt><br /><tt><font
size="2">> </font></tt><br /><tt><font size="2">> Where getrows is the type created by me;</font></tt><br
/><tt><fontsize="2">> It is created successfully.</font></tt><br /><tt><font size="2">> I am trying to call the
functionI pgadmin with the following command </font></tt><br /><tt><font size="2">> select * from
getrowstest2('general_user','aipl@123');</font></tt><br/><tt><font size="2">>  </font></tt><br /><tt><font
size="2">>I am getting the following error </font></tt><br /><tt><font size="2">>  </font></tt><br /><tt><font
size="2">>ERROR:  there is no parameter $1</font></tt><br /><tt><font size="2">> LINE 5: r.role_id = u.role_id
andp.name= $1 and p.password = $2</font></tt><br /><tt><font size="2">>                                          
^</font></tt><br/><tt><font size="2">> QUERY:  select <br />>
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,</font></tt><br
/><tt><fontsize="2">>
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,</font></tt><br
/><tt><fontsize="2">>
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,</font></tt><br
/><tt><fontsize="2">>
r.right_route_analysis,r.right_legend,r.right_treeview,r.insert,r.update,r.delete,r.query,r.lock<br/>> from
ksdi.user_rolesu,ksdi.principals p,ksdi.roles r where <br />> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br
/><tt><fontsize="2">> r.role_id = u.role_id and p.name= $1 and p.password = $2</font></tt><br /><tt><font
size="2">>CONTEXT:  PL/pgSQL function "getrowstest2" line 8 at FOR over <br />> EXECUTE statement</font></tt><br
/><br/><tt><font size="2">You have named the parametes in the arguments list:</font></tt><br /><br /><tt><font
size="2">getrowstest3(pname character varying,ppassword character varying)</font></tt><br /><br /><tt><font
size="2">trythe sql with:</font></tt><br /><tt><font size="2"> p.name =pname  and p.password =
ppassword;</font></tt><br/><br /><tt><font size="2">Cheers</font></tt><br /><br /><tt><font
size="2">Martin</font></tt><fontface="sans-serif">============================================= Romax Technology
LimitedRutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44
(0)115951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact
================================================ E-mail: info@romaxtech.com Website: www.romaxtech.com
================================================= Confidentiality Statement This transmission is for the addressee only
andcontains information that is confidential and privileged. Unless you are the named addressee, or authorised to
receiveit on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received
thistransmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================</font>