OK. Thanks, Andrey.
2010/12/14 Pavel Stehule <pavel.steh...@gmail.com>: > Hello > > 2010/12/14 Andrey G. <andv...@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.steh...@gmail.com>: >>> Hello >>> >>> View must not has a parameter in PostgreSQL. You can use a SRF function: >>> >>> postgres=# create or replace function parametrized_view(a int) >>> returns setof foo as $$ >>> select * from foo where a = $1; >>> $$ language sql immutable; >>> CREATE FUNCTION >>> postgres=# select * from parametrized_view(10); >>> a >>> ---- >>> 10 >>> (1 row) >>> >>> postgres=# explain select * from parametrized_view(10); >>> QUERY PLAN >>> -------------------------------------------------------------- >>> Index Scan using aa on foo (cost=0.00..8.27 rows=1 width=4) >>> Index Cond: (a = 10) >>> (2 rows) >>> >>> Regards >>> >>> Pavel Stehule >>> >>> >>> 2010/12/13 Andrey G. <andv...@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: 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 <robertmh...@gmail.com> >>>>> >>>>> On Tue, Nov 30, 2010 at 4:43 PM, Andrey Galkin <andv...@gmail.com> wrote: >>>>> > >>>>> > The following bug has been logged online: >>>>> > >>>>> > Bug reference: 5776 >>>>> > Logged by: Andrey Galkin >>>>> > Email address: andv...@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 >>>> >>>> >>>> -- >>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-bugs >>>> >>>> >>> >> > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
