From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Divyaprakash Y <divyaprakash(dot)y(at)celstream(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Create view is not accepting the parameter in postgres functions |
Date: | 2012-06-13 17:02:02 |
Message-ID: | CAHyXU0y=2AVPb0ThOJ644gLLe8Cz89gAS8KfWunS4kigXW7tpg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-13 17:25:27 | Re: Create view is not accepting the parameter in postgres functions |
Previous Message | leaf_yxj | 2012-06-13 17:01:32 | Re: How to create c language in postgresql database. Thanks. |