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

On 06/13/2012 01:31 PM, Divyaprakash Y wrote:

> 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";
....
> 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;

Heh, that's an interesting one. What version of PostgreSQL are you using?

What exactly are you trying to accomplish with this? What problem are
you trying to solve?


On 9.1.3 I'm getting:

CREATE OR REPLACE FUNCTION test(integer) RETURNS setof "A" AS $$
CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = $1;
SELECT * FROM "B";
$$ LANGUAGE 'sql';

ERROR:  relation "B" does not exist
LINE 3: SELECT * FROM "B";

... where "A" of course exists.

I would not expect this to work, because AFAIK sql functions are
prepared and have their plans saved either when first run or when
created, one of the two. What you'll need is a PL/PgSQL function that
uses the 'EXECUTE' statement to create the view dynamically, eg:

CREATE OR REPLACE FUNCTION test(integer) RETURNS SETOF "A" AS $$
BEGIN
         EXECUTE 'CREATE VIEW "B" AS SELECT * FROM "A" WHERE id =
'||quote_literal($1);
         RETURN QUERY SELECT * FROM "B";
END;
$$ LANGUAGE 'plpgsql';

Note that this will fail when run a second time. You will have to DROP
the view, and you will be subject to all sorts of exciting cross-session
race conditions. You can use CREATE TEMPORARY VIEW, but that'll still
exist until the session ends. Both approaches are slow.

That leads back to my first question: What exactly are you trying to
accomplish with this? What problem are you trying to solve?

--
Craig Ringer