Thread: ERROR: there is no parameter $1

ERROR: there is no parameter $1

From
"Madhu.Lanka"
Date:

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

 

 

 

Re: ERROR: there is no parameter $1

From
Sergey Konoplev
Date:
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

Re: ERROR: there is no parameter $1

From
"Martin French"
Date:
<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>

Re: ERROR: there is no parameter $1

From
"Martin French"
Date:
<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>

Re: ERROR: there is no parameter $1

From
Bill MacArthur
Date:

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;