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

I think temp table, would be better option if you must decide from some
reason...

However, why would you use View or temp table in that scenario? I mean what
would be wrong with:

CREATE OR REPLACE FUNCTION "MyFun"(INTEGER)
RETURNS SETOF "B" AS
$BODY$
SELECT * FROM "B";
$BODY$
LANGUAGE 'sql' STABLE
COST 100;

Kind Regards,

Misa

2012/6/13 Divyaprakash Y <divyaprakash(dot)y(at)celstream(dot)com>

> 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(at)ringerc(dot)id(dot)au]
> Sent: Wednesday, June 13, 2012 12:39 PM
> To: Divyaprakash Y
> Cc: pgsql-general(at)postgresql(dot)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(at)celstream(dot)com immediately.
>
> ______________________________________________________________________________
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>