Message-ID: <50095423.9030905@dhs-club.com>
Date: Fri, 20 Jul 2012 08:50:43 -0400
From: Bill MacArthur <webmaster@dhs-club.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org, mlanka@avineonindia.com
Subject: Re: ERROR:  there is no parameter $1
References: <000901cd6620$10857220$31905660$@avineonindia.com>
In-Reply-To: <000901cd6620$10857220$31905660$@avineonindia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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;