Re: Dynamic views

On Wed, 29 Nov 2006 18:38:22 +0530, "Merlin Moncure" <mmoncure(at)gmail(dot)com> wrote:
> On 11/29/06, vivek(at)staff(dot)ownmail(dot)com <vivek(at)staff(dot)ownmail(dot)com> wrote:
>> Hello friends,
>> I have a view defined as:-
>>
>> CREATE VIEW vivek_testview AS SELECT vivek_test.* , users.username AS
> name from users, vivek_test where vivek_test.username=users.username;
>>
>> Now, when I add a new column in vivek_test, I cant see the new column in
> the view. Currently we have to drop the view and recreate it.
>> Is there a way that i can see the new column without dropping and
> recreating the view ?
>>

No, not easy. Do you have to change your database schema that often?

>> Thanks for reading this. Thanks for your replies in advance.
>
> 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.

Thanks,

Bernd