pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: there is no parameter $1
5+ messages / 4 participants
[nested] [flat]
* ERROR: there is no parameter $1
@ 2012-07-20 02:33 Madhu.Lanka <mlanka@avineonindia.com>
0 siblings, 3 replies; 5+ messages in thread
From: Madhu.Lanka @ 2012-07-20 02:33 UTC (permalink / raw)
To: pgsql-admin
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','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.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR: there is no parameter $1
@ 2012-07-20 04:25 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
parent: Madhu.Lanka <mlanka@avineonindia.com>
2 siblings, 1 reply; 5+ messages in thread
From: Sergey Konoplev @ 2012-07-20 04:25 UTC (permalink / raw)
To: mlanka@avineonindia.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR: there is no parameter $1
@ 2012-07-20 06:34 Martin French <Martin.French@romaxtech.com>
parent: Madhu.Lanka <mlanka@avineonindia.com>
2 siblings, 0 replies; 5+ messages in thread
From: Martin French @ 2012-07-20 06:34 UTC (permalink / raw)
To: mlanka@avineonindia.com; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org
<html><body><p><font size="2" face="sans-serif">Hi </font><br><br><tt><font size="2">pgsql-admin-owner@postgresql.org wrote on 20/07/2012 03: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><font size="2">> Sent by: pgsql-admin-owner@postgresql.org</font></tt><br><tt><font size="2">> <br>> Hi Friends</font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> I am creating the function like </font></tt><br><tt><font size="2">> </font></tt><br><tt><font size="2">> CREATE OR REPLACE FUNCTION getrowstest3(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><font size="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><font size="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><font size="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_roles u,ksdi.principals p,ksdi.roles r where <br>> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br><tt><font size="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><font size="2">> $BODY$</font></tt><br><tt><font size="2">> LANGUAGE plpgsql VOLATILE</font></tt><br><tt><font size="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><font size="2">> It is created successfully.</font></tt><br><tt><font size="2">> I am trying to call the function I 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 and p.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><font size="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><font size="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><font size="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_roles u,ksdi.principals p,ksdi.roles r where <br>> p.PRINCIPAL_ID = u.USER_ID and </font></tt><br><tt><font size="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">try the 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><font face="sans-serif">=============================================
Romax Technology Limited
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 and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================</font>
</body></html>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR: there is no parameter $1
@ 2012-07-20 07:30 Martin French <Martin.French@romaxtech.com>
parent: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 0 replies; 5+ messages in thread
From: Martin French @ 2012-07-20 07:30 UTC (permalink / raw)
To: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>; +Cc: mlanka@avineonindia.com; pgsql-admin; pgsql-admin-owner@postgresql.org
--0__=0FBBF0D2DFBABA268f9e8a93df938690918c0FBBF0D2DFBABA26
Content-Type: text/html; charset=WINDOWS-1252
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
<html><body><p><font size=3D"2" face=3D"sans-serif">That's a much better an=
swer than mine! </font><br><br><font size=3D"2" face=3D"sans-serif">Take th=
is advice! ;)</font><br><br><font size=3D"2" face=3D"sans-serif">Chee=
rs</font><br><br><img width=3D"16" height=3D"16" src=3D"cid:0__=3D0FBBF0D2D=
FBABA268f9e8a93df@romaxtech.com" border=3D"0" alt=3D"Inactive hide details =
for Sergey Konoplev ---20/07/2012 08:29:18---On Fri, Jul 20, 2012 at 6:33 A=
M, Madhu.Lanka <mlanka@avineon"><font size=3D"2" color=3D"#424282" face=
=3D"sans-serif">Sergey Konoplev ---20/07/2012 08:29:18---On Fri, Jul 20, 20=
12 at 6:33 AM, Madhu.Lanka <mlanka@avineonindia.com> wrote: > I am=
getting the fol</font><br><br><font size=3D"1" color=3D"#5F5F5F" face=3D"s=
ans-serif">From: </font><font size=3D"1" face=3D"sans-serif">Sergey =
Konoplev <sergey.konoplev@postgresql-consulting.com></font><br><font =
size=3D"1" color=3D"#5F5F5F" face=3D"sans-serif">To: </font><font si=
ze=3D"1" face=3D"sans-serif">mlanka@avineonindia.com, </font><br><font size=
=3D"1" color=3D"#5F5F5F" face=3D"sans-serif">Cc: </font><font size=
=3D"1" face=3D"sans-serif">pgsql-admin@postgresql.org</font><br><font size=
=3D"1" color=3D"#5F5F5F" face=3D"sans-serif">Date: </font><font size=
=3D"1" face=3D"sans-serif">20/07/2012 08:29</font><br><font size=3D"1" colo=
r=3D"#5F5F5F" face=3D"sans-serif">Subject: </font><font size=3D"1" f=
ace=3D"sans-serif">Re: [ADMIN] ERROR: there is no parameter $1</font><br><f=
ont size=3D"1" color=3D"#5F5F5F" face=3D"sans-serif">Sent by: </font=
><font size=3D"1" face=3D"sans-serif">pgsql-admin-owner@postgresql.org</fon=
t><br><hr width=3D"100%" size=3D"2" align=3D"left" noshade style=3D"color:#=
8091A5; "><br><br><br><tt><font size=3D"2">On Fri, Jul 20, 2012 at 6:33 AM,=
Madhu.Lanka <mlanka@avineonindia.com> wrote:<br>> I am getting th=
e following error<br>><br>> ERROR: there is no parameter $1<br>=
> LINE 5: r.role_id =3D u.role_id and p.name=3D $1 and p.password =3D $2=
<br><br>Because they are not expanding inside strings. Use EXECUTE ... USIN=
G ...<br><br>EXECUTE '... and p.name =3D$1 and p.password =3D $2;' US=
ING pname, ppassword<br><br>-- <br>Sergey Konoplev<br><br>a database archit=
ect, software developer at PostgreSQL-Consulting.com<br></font></tt><tt><fo=
nt size=3D"2"><a href=3D"http://www.postgresql-consulting.com">http://www.p=
ostgresql-consulting.com</a></font></tt><tt><font size=3D"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 chang=
es to your subscription:<br></font></tt><tt><font size=3D"2"><a href=3D"htt=
p://www.postgresql.org/mailpref/pgsql-admin">http://www.postgresql.org/mail=
pref/pgsql-admin</a></font></tt><tt><font size=3D"2"><br><br></font></tt><b=
r><font face=3D"sans-serif">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,=20
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Confidentiality Statement
This transmission is for the addressee only and contains information that i=
s confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf o=
f the addressee=20
you may not copy or use it, or disclose it to anyone else.=20
If you have received this transmission in error please delete from your sys=
tem and contact the sender. Thank you for your cooperation.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</f=
ont>
</body></html>
--0__=0FBBF0D2DFBABA268f9e8a93df938690918c0FBBF0D2DFBABA26
Content-type: image/gif;
name="graycol.gif"
Content-Disposition: inline; filename="graycol.gif"
Content-ID: <0__=0FBBF0D2DFBABA268f9e8a93df@romaxtech.com>
Content-Transfer-Encoding: base64
R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu
ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7
--0__=0FBBF0D2DFBABA268f9e8a93df938690918c0FBBF0D2DFBABA26--
Attachments:
graycol.gif (image/gif, 105B) download
view image
* Re: ERROR: there is no parameter $1
@ 2012-07-20 12:50 Bill MacArthur <webmaster@dhs-club.com>
parent: Madhu.Lanka <mlanka@avineonindia.com>
2 siblings, 0 replies; 5+ messages in thread
From: Bill MacArthur @ 2012-07-20 12:50 UTC (permalink / raw)
To: pgsql-admin; mlanka@avineonindia.com
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-07-20 12:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-20 02:33 ERROR: there is no parameter $1 Madhu.Lanka <mlanka@avineonindia.com>
2012-07-20 04:25 ` Re: ERROR: there is no parameter $1 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-07-20 07:30 ` Re: ERROR: there is no parameter $1 Martin French <Martin.French@romaxtech.com>
2012-07-20 06:34 ` Martin French <Martin.French@romaxtech.com>
2012-07-20 12:50 ` Bill MacArthur <webmaster@dhs-club.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox