Create view is not accepting the parameter in postgres functions

["Divyaprakash Y" <divyaprakash.y@xxxxxxxxxxxxx>]

Hi,

 

Is the following postgres function correct?

 

CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)

                RETURNS SETOF "B" AS

$BODY$

                CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;

                SELECT * FROM "B";

$BODY$

  LANGUAGE 'sql' VOLATILE

  COST 100;

ALTER FUNCTION "MyFun"(INTEGER) OWNER TO postgres;

 

Where “B” is a table in the DB schema.

 

 

Executing “select * from "MyFun"(1) “ throws the following error:

 

ERROR:  there is no parameter $1

LINE 2: ...W "A” AS SELECT * FROM "B" WHERE "Id" = $1;

 

----------------

 

Where as the following function works fine:

 

CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)

                RETURNS SETOF "B" AS

$BODY$

                CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = 1;

                SELECT * FROM "B";

$BODY$

  LANGUAGE 'sql' VOLATILE

  COST 100;

ALTER FUNCTION "MyFun"(INTEGER) OWNER TO postgres;

 

Where “Id” is hardcoded within the function.

 

Is this expected? Please reply.

 

Regards,

DP

 

______________________________________________________________________________
 DISCLAIMER: This electronic message and any attachments to this electronic
 message is intended for the exclusive use of the addressee(s) named herein
 and may contain legally privileged and confidential information. It is the 
 property of Celstream Technologies Pvt Limited. If you are not the intended
 recipient, you are hereby strictly notified not to copy, forward, distribute
 or use this message or any attachments thereto. If you have received this
 message in error, please delete it and all copies thereof, from your system
 and notify the sender at Celstream Technologies or 
 administrator@xxxxxxxxxxxxx immediately.
______________________________________________________________________________