Thread: Dynamic views

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
andrecreate it.  
Is there a way that i can see the new column without dropping and recreating the view ?

Thanks for reading this. Thanks for your replies in advance.



With warm regards.
Vivek

vivek@staff.ownmail.com

All science is either physics or stamp collecting.
                -- Ernest Rutherford

On 11/29/06, vivek@staff.ownmail.com <vivek@staff.ownmail.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
andrecreate it. 
> Is there a way that i can see the new column without dropping and recreating the view ?
>
> 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.

merlin

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

On Wed, 29 Nov 2006 18:38:22 +0530, "Merlin Moncure" <mmoncure@gmail.com> wrote:
> On 11/29/06, vivek@staff.ownmail.com <vivek@staff.ownmail.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