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