Обсуждение: BUG #5776: Unable to create view with parameter in PL/pgsql
The following bug has been logged online:
Bug reference: 5776
Logged by: Andrey Galkin
Email address: andvgal@gmail.com
PostgreSQL version: 9.0.1
Operating system: Debian unstable
Description: Unable to create view with parameter in PL/pgsql
Details:
Below is simple test case. Perhaps, I'm doing something wrong.
--
============================================================================
START TRANSACTION;
CREATE TABLE t1 (
some_field INT
);
--
-- WORKS
--
CREATE FUNCTION Bug_create_tmp_view_test()
RETURNS VOID
AS $$
BEGIN
CREATE TEMPORARY VIEW v1 AS
SELECT * FROM t1 WHERE some_field = 1;
END;
$$
LANGUAGE plpgsql;
--
-- FAILS: ERROR: column "v_some_field" does not exist
--
CREATE FUNCTION Bug_create_tmp_view_test( v_some_field INT4 )
RETURNS VOID
AS $$
BEGIN
CREATE TEMPORARY VIEW v2 AS
SELECT * FROM t1 WHERE some_field = v_some_field;
END;
$$
LANGUAGE plpgsql;
--
-- WORKS
--
CREATE FUNCTION Bug_create_tmp_view_exec_test()
RETURNS VOID
AS $$
BEGIN
EXECUTE 'CREATE VIEW v3 AS SELECT * FROM t1 WHERE some_field = 1';
END;
$$
LANGUAGE plpgsql;
--
-- FAILS
--
CREATE FUNCTION Bug_create_tmp_view_exec_test( v_some_field INT4 )
RETURNS VOID
AS $$
BEGIN
EXECUTE 'INSERT INTO t1 (some_field) VALUES ( $1 )' USING v_some_field;
EXECUTE 'CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1' USING
v_some_field;
END;
$$
LANGUAGE plpgsql;
--
-- Comment the line below to trigger the second form of the/related bug
SELECT Bug_create_tmp_view_test(), Bug_create_tmp_view_test( 1 );
SELECT Bug_create_tmp_view_exec_test(), Bug_create_tmp_view_exec_test( 1 );
ROLLBACK;
--
============================================================================
$ psql -q < pgbug_create_statement.sql
ERROR: column "v_some_field" does not exist
LINE 2: SELECT * FROM t1 WHERE some_field = v_some_field
^
QUERY: CREATE TEMPORARY VIEW v2 AS
SELECT * FROM t1 WHERE some_field = v_some_field
CONTEXT: PL/pgSQL function "bug_create_tmp_view_test" line 2 at SQL
statement
$ psql -q < pgbug_create_statement.sql
ERROR: there is no parameter $1
LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
^
QUERY: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1
CONTEXT: PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
EXECUTE statement
On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05776 > Logged by: =A0 =A0 =A0 =A0 =A0Andrey Galkin > Email address: =A0 =A0 =A0andvgal@gmail.com > PostgreSQL version: 9.0.1 > Operating system: =A0 Debian unstable > Description: =A0 =A0 =A0 =A0Unable to create view with parameter in PL/pg= sql > Details: > > Below is simple test case. Perhaps, I'm doing something wrong. You can accomplish what you're trying to do using EXECUTE. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
It seems my original test, which also includes the EXECUTE approach, has not come to you in full. EXECUTE statement also fails with parameter: The test is attached in file. psql -q < db/db/pgbug_5776.sql ERROR: there is no parameter $1 LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1 ^ QUERY: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field = $1 CONTEXT: PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at EXECUTE statement Andrey 2010/12/13 Robert Haas <robertmhaas@gmail.com> > > On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote: > > > > The following bug has been logged online: > > > > Bug reference: 5776 > > Logged by: Andrey Galkin > > Email address: andvgal@gmail.com > > PostgreSQL version: 9.0.1 > > Operating system: Debian unstable > > Description: Unable to create view with parameter in PL/pgsql > > Details: > > > > Below is simple test case. Perhaps, I'm doing something wrong. > > You can accomplish what you're trying to do using EXECUTE. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
Вложения
Hello
View must not has a parameter in PostgreSQL. You can use a SRF function:
postgres=3D# create or replace function parametrized_view(a int)
returns setof foo as $$
select * from foo where a =3D $1;
$$ language sql immutable;
CREATE FUNCTION
postgres=3D# select * from parametrized_view(10);
a
----
10
(1 row)
postgres=3D# explain select * from parametrized_view(10);
QUERY PLAN
--------------------------------------------------------------
Index Scan using aa on foo (cost=3D0.00..8.27 rows=3D1 width=3D4)
Index Cond: (a =3D 10)
(2 rows)
Regards
Pavel Stehule
2010/12/13 Andrey G. <andvgal@gmail.com>:
> It seems my original test, which also includes the EXECUTE approach,
> has not come to you in full. EXECUTE statement also fails with
> parameter: The test is attached in file.
>
> psql -q < db/db/pgbug_5776.sql
> ERROR:=C2=A0 there is no parameter $1
> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^
> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1
> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at
> EXECUTE statement
>
> Andrey
>
>
> 2010/12/13 Robert Haas <robertmhaas@gmail.com>
>>
>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrote:
>> >
>> > The following bug has been logged online:
>> >
>> > Bug reference: =C2=A0 =C2=A0 =C2=A05776
>> > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Andrey Galkin
>> > Email address: =C2=A0 =C2=A0 =C2=A0andvgal@gmail.com
>> > PostgreSQL version: 9.0.1
>> > Operating system: =C2=A0 Debian unstable
>> > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Unable to create view with par=
ameter in PL/pgsql
>> > Details:
>> >
>> > Below is simple test case. Perhaps, I'm doing something wrong.
>>
>> You can accomplish what you're trying to do using EXECUTE.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
Hello 2010/12/14 Andrey G. <andvgal@gmail.com>: > Pavel, > > As far as I'm aware, SETOF return creates whole result in memory, what > has some implications. > it's not true for immutable SQL function - look on EXPLAIN > My intention was to create a temporary view in session, which can be > used in other processing. Of course, I've found another solution, but > creating view with parameter dynamically could be a good feature. > It's done - SQL immutable function works exactly like you need. Regards Pavel Stehule > Andrey > > > > 2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>: >> Hello >> >> View must not has a parameter in PostgreSQL. You can use a SRF function: >> >> postgres=3D# create or replace function parametrized_view(a int) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0returns se= tof foo as $$ >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 se= lect * from foo where a =3D $1; >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$$ languag= e sql immutable; >> CREATE FUNCTION >> postgres=3D# select * from parametrized_view(10); >> =C2=A0a >> ---- >> =C2=A010 >> (1 row) >> >> postgres=3D# explain select * from parametrized_view(10); >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0QUERY PLAN >> -------------------------------------------------------------- >> =C2=A0Index Scan using aa on foo =C2=A0(cost=3D0.00..8.27 rows=3D1 width= =3D4) >> =C2=A0 Index Cond: (a =3D 10) >> (2 rows) >> >> Regards >> >> Pavel Stehule >> >> >> 2010/12/13 Andrey G. <andvgal@gmail.com>: >>> It seems my original test, which also includes the EXECUTE approach, >>> has not come to you in full. EXECUTE statement also fails with >>> parameter: The test is attached in file. >>> >>> psql -q < db/db/pgbug_5776.sql >>> ERROR:=C2=A0 there is no parameter $1 >>> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >>> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^ >>> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >>> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3= at >>> EXECUTE statement >>> >>> Andrey >>> >>> >>> 2010/12/13 Robert Haas <robertmhaas@gmail.com> >>>> >>>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wro= te: >>>> > >>>> > The following bug has been logged online: >>>> > >>>> > Bug reference: =C2=A0 =C2=A0 =C2=A05776 >>>> > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Andrey Galkin >>>> > Email address: =C2=A0 =C2=A0 =C2=A0andvgal@gmail.com >>>> > PostgreSQL version: 9.0.1 >>>> > Operating system: =C2=A0 Debian unstable >>>> > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Unable to create view with p= arameter in PL/pgsql >>>> > Details: >>>> > >>>> > Below is simple test case. Perhaps, I'm doing something wrong. >>>> >>>> You can accomplish what you're trying to do using EXECUTE. >>>> >>>> -- >>>> Robert Haas >>>> EnterpriseDB: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>> >>> >>> -- >>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-bugs >>> >>> >> >
Pavel, As far as I'm aware, SETOF return creates whole result in memory, what has some implications. My intention was to create a temporary view in session, which can be used in other processing. Of course, I've found another solution, but creating view with parameter dynamically could be a good feature. Andrey 2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>: > Hello > > View must not has a parameter in PostgreSQL. You can use a SRF function: > > postgres=3D# create or replace function parametrized_view(a int) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0returns set= of foo as $$ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sel= ect * from foo where a =3D $1; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$$ language= sql immutable; > CREATE FUNCTION > postgres=3D# select * from parametrized_view(10); > =C2=A0a > ---- > =C2=A010 > (1 row) > > postgres=3D# explain select * from parametrized_view(10); > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0QUERY PLAN > -------------------------------------------------------------- > =C2=A0Index Scan using aa on foo =C2=A0(cost=3D0.00..8.27 rows=3D1 width= =3D4) > =C2=A0 Index Cond: (a =3D 10) > (2 rows) > > Regards > > Pavel Stehule > > > 2010/12/13 Andrey G. <andvgal@gmail.com>: >> It seems my original test, which also includes the EXECUTE approach, >> has not come to you in full. EXECUTE statement also fails with >> parameter: The test is attached in file. >> >> psql -q < db/db/pgbug_5776.sql >> ERROR:=C2=A0 there is no parameter $1 >> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^ >> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 = at >> EXECUTE statement >> >> Andrey >> >> >> 2010/12/13 Robert Haas <robertmhaas@gmail.com> >>> >>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wrot= e: >>> > >>> > The following bug has been logged online: >>> > >>> > Bug reference: =C2=A0 =C2=A0 =C2=A05776 >>> > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Andrey Galkin >>> > Email address: =C2=A0 =C2=A0 =C2=A0andvgal@gmail.com >>> > PostgreSQL version: 9.0.1 >>> > Operating system: =C2=A0 Debian unstable >>> > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Unable to create view with pa= rameter in PL/pgsql >>> > Details: >>> > >>> > Below is simple test case. Perhaps, I'm doing something wrong. >>> >>> You can accomplish what you're trying to do using EXECUTE. >>> >>> -- >>> Robert Haas >>> EnterpriseDB: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >> >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> >> >
On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal@gmail.com> wrote: > It seems my original test, which also includes the EXECUTE approach, > has not come to you in full. EXECUTE statement also fails with > parameter: The test is attached in file. > > psql -q < db/db/pgbug_5776.sql > ERROR:=A0 there is no parameter $1 > LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ^ > QUERY:=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 > CONTEXT:=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 at > EXECUTE statement Yeah, parameter substitution doesn't work in this case. You could however build up a string with quote_identifier() and then EXECUTE the resulting string. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Dec 14, 2010 at 4:03 PM, Andrey G. <andvgal@gmail.com> wrote: > Yes, you've read my mind about quote_literal() ;). The bug report is > rising exactly the parameter substitution issue. Yeah. I don't think it's exactly a bug so much as a known limitation, but of course I agree it would be convenient if it worked differently. Nobody's felt the urge to do the work necessary to remove that limitation yet, though... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
OK. Thanks, Andrey. 2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>: > Hello > > 2010/12/14 Andrey G. <andvgal@gmail.com>: >> Pavel, >> >> As far as I'm aware, SETOF return creates whole result in memory, what >> has some implications. >> > > it's not true for immutable SQL function - look on EXPLAIN > >> My intention was to create a temporary view in session, which can be >> used in other processing. Of course, I've found another solution, but >> creating view with parameter dynamically could be a good feature. >> > > It's done - SQL immutable function works exactly like you need. > > Regards > > Pavel Stehule > > >> Andrey >> >> >> >> 2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>: >>> Hello >>> >>> View must not has a parameter in PostgreSQL. You can use a SRF function: >>> >>> postgres=3D# create or replace function parametrized_view(a int) >>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0returns s= etof foo as $$ >>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s= elect * from foo where a =3D $1; >>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$$ langua= ge sql immutable; >>> CREATE FUNCTION >>> postgres=3D# select * from parametrized_view(10); >>> =C2=A0a >>> ---- >>> =C2=A010 >>> (1 row) >>> >>> postgres=3D# explain select * from parametrized_view(10); >>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0QUERY PLAN >>> -------------------------------------------------------------- >>> =C2=A0Index Scan using aa on foo =C2=A0(cost=3D0.00..8.27 rows=3D1 widt= h=3D4) >>> =C2=A0 Index Cond: (a =3D 10) >>> (2 rows) >>> >>> Regards >>> >>> Pavel Stehule >>> >>> >>> 2010/12/13 Andrey G. <andvgal@gmail.com>: >>>> It seems my original test, which also includes the EXECUTE approach, >>>> has not come to you in full. EXECUTE statement also fails with >>>> parameter: The test is attached in file. >>>> >>>> psql -q < db/db/pgbug_5776.sql >>>> ERROR:=C2=A0 there is no parameter $1 >>>> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >>>> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^ >>>> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >>>> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line = 3 at >>>> EXECUTE statement >>>> >>>> Andrey >>>> >>>> >>>> 2010/12/13 Robert Haas <robertmhaas@gmail.com> >>>>> >>>>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andvgal@gmail.com> wr= ote: >>>>> > >>>>> > The following bug has been logged online: >>>>> > >>>>> > Bug reference: =C2=A0 =C2=A0 =C2=A05776 >>>>> > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Andrey Galkin >>>>> > Email address: =C2=A0 =C2=A0 =C2=A0andvgal@gmail.com >>>>> > PostgreSQL version: 9.0.1 >>>>> > Operating system: =C2=A0 Debian unstable >>>>> > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Unable to create view with = parameter in PL/pgsql >>>>> > Details: >>>>> > >>>>> > Below is simple test case. Perhaps, I'm doing something wrong. >>>>> >>>>> You can accomplish what you're trying to do using EXECUTE. >>>>> >>>>> -- >>>>> Robert Haas >>>>> EnterpriseDB: http://www.enterprisedb.com >>>>> The Enterprise PostgreSQL Company >>>> >>>> >>>> -- >>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-bugs >>>> >>>> >>> >> >
Robert, Yes, you've read my mind about quote_literal() ;). The bug report is rising exactly the parameter substitution issue. Andrey 2010/12/14 Robert Haas <robertmhaas@gmail.com>: > On Mon, Dec 13, 2010 at 4:28 PM, Andrey G. <andvgal@gmail.com> wrote: >> It seems my original test, which also includes the EXECUTE approach, >> has not come to you in full. EXECUTE statement also fails with >> parameter: The test is attached in file. >> >> psql -q < db/db/pgbug_5776.sql >> ERROR:=C2=A0 there is no parameter $1 >> LINE 1: CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^ >> QUERY:=C2=A0 CREATE VIEW v4 AS SELECT * FROM t1 WHERE some_field =3D $1 >> CONTEXT:=C2=A0 PL/pgSQL function "bug_create_tmp_view_exec_test" line 3 = at >> EXECUTE statement > > Yeah, parameter substitution doesn't work in this case. =C2=A0You could > however build up a string with quote_identifier() and then EXECUTE the > resulting string. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >