Re: Dynamic views

On 11/29/06, Bernd Helmle <mailings@oopsware.de> wrote:
> > no, queries using * are expanded when the plan is created.  for views,
> > the plan is created when you create the view (also the original query
> > string to create the view is not stored).  however, you could however
> > create a function that returns setof record, because plans for
> > functions are created when they are first run in a session.  this
> > isn't a perfect solution, but it might work for you.
>
> But that requires to adjust all SELECTs which uses this table function, since
>
> SETOF RECORD requires you to specify a column list. Maybe you can build
>
> something like that using a table's implicit type.

correct:

create table foo (a text, b int, c int);
insert into foo values ('1', 2, 3);
create function f() returns setof foo as $$ select * from foo; $$ language sql;
select f();
    f
---------
 (1,2,3)
(1 row)

alter table foo add column d int default 4;

select * from f();
 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4

merlin