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

Woops,

I thought:

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

2012/6/13 Misa Simic <misa(dot)simic(at)gmail(dot)com>

> 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
>>
>
>