Обсуждение: ERROR: there is no parameter $1
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
On Fri, Jul 20, 2012 at 6:33 AM, Madhu.Lanka <mlanka@avineonindia.com> wrote: > 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 Because they are not expanding inside strings. Use EXECUTE ... USING ... EXECUTE '... and p.name =$1 and p.password = $2;' USING pname, ppassword -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
<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>
<p><font face="sans-serif" size="2">That's a much better answer than mine! </font><br /><br /><font face="sans-serif" size="2">Takethis advice! ;)</font><br /><br /><font face="sans-serif" size="2">Cheers</font><br /><br /><img alt="Inactivehide details for Sergey Konoplev ---20/07/2012 08:29:18---On Fri, Jul 20, 2012 at 6:33 AM, Madhu.Lanka <mlanka@avineon"border="0" height="16" src="cid:0__=0FBBF0D2DFBABA268f9e8a93df@romaxtech.com" width="16" /><font color="#424282"face="sans-serif" size="2">Sergey Konoplev ---20/07/2012 08:29:18---On Fri, Jul 20, 2012 at 6:33 AM, Madhu.Lanka<mlanka@avineonindia.com> wrote: > I am getting the fol</font><br /><br /><font color="#5F5F5F" face="sans-serif"size="1">From: </font><font face="sans-serif" size="1">Sergey Konoplev <sergey.konoplev@postgresql-consulting.com></font><br/><font color="#5F5F5F" face="sans-serif" size="1">To: </font><fontface="sans-serif" size="1">mlanka@avineonindia.com, </font><br /><font color="#5F5F5F" face="sans-serif" size="1">Cc:</font><font face="sans-serif" size="1">pgsql-admin@postgresql.org</font><br /><font color="#5F5F5F" face="sans-serif"size="1">Date: </font><font face="sans-serif" size="1">20/07/2012 08:29</font><br /><font color="#5F5F5F"face="sans-serif" size="1">Subject: </font><font face="sans-serif" size="1">Re: [ADMIN] ERROR: there is noparameter $1</font><br /><font color="#5F5F5F" face="sans-serif" size="1">Sent by: </font><font face="sans-serif" size="1">pgsql-admin-owner@postgresql.org</font><br/><hr align="left" noshade size="2" style="color:#8091A5; " width="100%"/><br /><br /><br /><tt><font size="2">On Fri, Jul 20, 2012 at 6:33 AM, Madhu.Lanka <mlanka@avineonindia.com>wrote:<br />> I am getting the following error<br />><br />> ERROR: there is noparameter $1<br />> LINE 5: r.role_id = u.role_id and p.name= $1 and p.password = $2<br /><br />Because they are notexpanding inside strings. Use EXECUTE ... USING ...<br /><br />EXECUTE '... and p.name =$1 and p.password = $2;' USINGpname, ppassword<br /><br />-- <br />Sergey Konoplev<br /><br />a database architect, software developer at PostgreSQL-Consulting.com<br/></font></tt><tt><font size="2"><a href="http://www.postgresql-consulting.com">http://www.postgresql-consulting.com</a></font></tt><tt><fontsize="2"><br /><br/>Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204<br /><br />-- <br />Sent via pgsql-admin mailing list(pgsql-admin@postgresql.org)<br />To make changes to your subscription:<br /></font></tt><tt><font size="2"><a href="http://www.postgresql.org/mailpref/pgsql-admin">http://www.postgresql.org/mailpref/pgsql-admin</a></font></tt><tt><font size="2"><br/><br /></font></tt><br /><font face="sans-serif">============================================= Romax TechnologyLimited Rutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers:+44 (0)115 951 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>
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.principalsp,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.principalsp,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.principalsp,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;