Thread: Permanent alias for postgresql table

Hi list,

I'm searching for a way to create permanent alias for
tablenames in postgresql. We are storing various versions
of a routable network in postgresql (postgis, pgrouting)
and access a certain version with a bunch of php-skripts.
We like to use aliases for the "currently used tables" oo
be able to relink the current tables rapidly by changing
the alias target.

Any idea - or is this approach nonsense?

Marco

Marco Lechner, 12.03.2009 13:59:
> Hi list,
> 
> I'm searching for a way to create permanent alias for
> tablenames in postgresql. We are storing various versions
> of a routable network in postgresql (postgis, pgrouting)
> and access a certain version with a bunch of php-skripts.
> We like to use aliases for the "currently used tables" oo
> be able to relink the current tables rapidly by changing
> the alias target.
> 
> Any idea - or is this approach nonsense?

A view? 

CREATE VIEW constant_table_name
AS 
SELECT *
FROM current_table

Thomas

Hi Mina,

thanks for your answer. I thought about that, but don't
views decrease performance, because they are "calculated"
on access?

Marco

On Thu, 12 Mar 2009 13:34:39 +0000Mina R Waheeb <syncer@gmail.com> wrote:
> Use views,
> 
> mytablev1 and we have a view mytable selecting * from
> mytablev1
> and when we need to update the target we alter the view
> to select from
> mytablev2
> 
> On Thu, Mar 12, 2009 at 12:59 PM, Marco Lechner <
> marco.lechner@geographie.uni-freiburg.de> wrote:
> 
> > Hi list,
> >
> > I'm searching for a way to create permanent alias for
> > tablenames in postgresql. We are storing various
> versions
> > of a routable network in postgresql (postgis,
> pgrouting)
> > and access a certain version with a bunch of
> php-skripts.
> > We like to use aliases for the "currently used tables"
> oo
> > be able to relink the current tables rapidly by
> changing
> > the alias target.
> >
> > Any idea - or is this approach nonsense?
> >
> > Marco
> >
> > --
> > Sent via pgsql-sql mailing list
> (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >

On Thu, Mar 12, 2009 at 03:26:47PM +0100, Marco Lechner wrote:
> Hi Mina,
>
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are "calculated"
> on access?

The query gets rewritten a bit, but it's not a big deal. A more
important concern might be that to make it so you can add / modify data
in the table, you'll need to create rules to rewrite UPDATE and INSERT
queries on that view to instead affect the underlying table.

- Josh / eggyknap

Marco Lechner, 12.03.2009 15:26:
> Hi Mina,
> 
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are "calculated"
> on access?

I'm not sure what you mean with "calculated". A view is just a SQL query.
There is no difference in executing the SQL query that's behind a view or the view itself. Except for the minimal time
ittakes to retrieve the view definition. 
 

But I would never sacrifice easy of development or usage for the microseconds of overhead the VIEW generates. 

And the "overhead" (if at all) will be neglectable compared to the time it takes to return the result.

Thomas

-- On Thu, 3/12/09, Marco Lechner <marco.lechner@geographie.uni-freiburg.de> wrote:

> From: Marco Lechner <marco.lechner@geographie.uni-freiburg.de>
> Subject: Re: [SQL] Permanent alias for postgresql table
> To: pgsql-sql@postgresql.org
> Date: Thursday, March 12, 2009, 2:26 PM
> Hi Mina,
> 
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are
> "calculated"
> on access?
> 
> Marco
> 
> On Thu, 12 Mar 2009 13:34:39 +0000
>  Mina R Waheeb <syncer@gmail.com> wrote:
> > Use views,
> > 
> > mytablev1 and we have a view mytable selecting * from
> > mytablev1
> > and when we need to update the target we alter the
> view
> > to select from
> > mytablev2
> > 
> > On Thu, Mar 12, 2009 at 12:59 PM, Marco Lechner <
> > marco.lechner@geographie.uni-freiburg.de> wrote:
> > 
> > > Hi list,
> > >
> > > I'm searching for a way to create permanent
> alias for
> > > tablenames in postgresql. We are storing various
> > versions
> > > of a routable network in postgresql (postgis,
> > pgrouting)
> > > and access a certain version with a bunch of
> > php-skripts.
> > > We like to use aliases for the "currently
> used tables"
> > oo
> > > be able to relink the current tables rapidly by
> > changing
> > > the alias target.
> > >
> > > Any idea - or is this approach nonsense?
> > >
> > > Marco
> > >
> > > --
> > > Sent via pgsql-sql mailing list
> > (pgsql-sql@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-sql
> > >
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

you can use partition table, whit a column whit the version of the data and create the partition for this column....