Create view is not accepting the parameter in postgres functions

On 13 Jun 2012, at 7:31, Divyaprakash Y wrote:

Hi,

Is the following postgres function correct?

CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)

Named parameters --------------------^^^

CREATE VIEW "A" AS SELECT * FROM "B" WHERE "Id" = $1;

Positional parameters ---------------------------------------------^^

You can't mix those. I don't think SQL functions support named parameters, so using positional parameters throughout would be the solution.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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

On 06/13/2012 03:06 PM, Alban Hertroys wrote:

Named parameters --------------------^^^
Positional parameters ---------------------------------------------^^

You can't mix those. I don't think SQL functions support named parameters, so using positional parameters throughout would be the solution.

Yep, that's the immediate cause of failure, and I missed that in my
reply so I'm answering the *next* question. Whoops, sorry.

If you remove the name and use positional style, the function still
fails for a different reason, as per my post following.

--
Craig Ringer

On Wed, Jun 13, 2012 at 12:06 AM, Alban Hertroys <haramrae@gmail.com> wrote:

On 13 Jun 2012, at 7:31, Divyaprakash Y wrote:

Hi,

Is the following postgres function correct?

CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)

Named parameters --------------------^^^

               CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;

Positional parameters ---------------------------------------------^^

You can't mix those. I don't think SQL functions support named parameters, so using positional parameters throughout would be the solution.

Funny, we have been mixing in this way since at least Pg 8.1 with no
problems just because the names are of semantic value to the
application, and SQL language functions don't support named arguments.
If this ever changes, I would certainly hope that the SQL language
functions would first be given named argument support.

You used to be able to mix directly in plpgsql iirc but I don't know
if that's still the case.

Best Wishes,
Chris Travers

Chris Travers, 13.06.2012 09:16:

If this ever changes, I would certainly hope that the SQL language
functions would first be given named argument support.

This is coming in 9.2

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

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

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.simic@gmail.com>

On Wed, Jun 13, 2012 at 12:31 AM, Divyaprakash Y
<divyaprakash.y@celstream.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

Merlin Moncure <mmoncure@gmail.com> writes:

The CREATE VIEW statement does not allow parameterized arguments
apparently.

Well, no. What would it mean? The view is likely to outlast the
existence of the function argument.

regards, tom lane

On Wed, Jun 13, 2012 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

The CREATE VIEW statement does not allow parameterized arguments
apparently.

Well, no.  What would it mean?  The view is likely to outlast the
existence of the function argument.

right -- it's quite sensible even if superficially unusual: for
vanilla queries there is a very clear line between what can be
parameterized and what can't. CREATE VIEW looks like a query but does
something completely different and so obeys a different set of rules.

merlin