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

Hey,

That works. Thanks for all the replies.

The answer for your questions
1. I am using Postgres 8.4.
2. That was the snippet which I was using for the further processing in
my function.

Also, few questions are as follows:
1. How different the positional parameter is from the named parameter?
2. I am able to use positional parameters in sql functions as in
PL/PgSQL function. This is the only case [create view] in which I could
not succeed.
3. Which would be faster..temp table or view?


-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Wednesday, June 13, 2012 12:39 PM
To: Divyaprakash Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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

______________________________________________________________________________
 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@celstream.com immediately.
______________________________________________________________________________