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
