Re: Create view is not accepting the parameter in postgres functions

On Wed, Jun 13, 2012 at 12:31 AM, Divyaprakash Y
<divyaprakash(dot)y(at)celstream(dot)com> wrote:
>
> 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.

(this has absolutely nothing to do with named parameters)

The CREATE VIEW statement does not allow parameterized arguments
apparently. If you want to do this, you have to switch to plpgsql and
use EXECUTE.

merlin