Postgres Pro
Downloads
Home   >   mailing lists

Thread: Solution for Synonyms

Solution for Synonyms

From
mrprice22
Date:
We are in the process of moving from Oracle to PostgreSQL.  We use a stored
procedure to populate some reporting tables once an hour.  There are two
sets of these tables, set A and set B.  We use synonyms to point to the
“active” set of tables at any given time.

The procedure works like this:

1. If set A tables are “active” truncate set B tables or if set A tables are
“active” truncate set B tables.
2. Populate set B tables.
3. Set synonyms to point to set B tables.

How might I accomplish the same thing in PostgreSQL?

Thanks,
Mark



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Solution-for-Synonyms-tp5779816.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Solution for Synonyms

From
Thomas Kellerer
Date:
mrprice22 wrote on 22.11.2013 19:25:
> We are in the process of moving from Oracle to PostgreSQL.  We use a stored
> procedure to populate some reporting tables once an hour.  There are two
> sets of these tables, set A and set B.  We use synonyms to point to the
> “active” set of tables at any given time.
>
> The procedure works like this:
>
> 1. If set A tables are “active” truncate set B tables or if set A tables are
> “active” truncate set B tables.
> 2. Populate set B tables.
> 3. Set synonyms to point to set B tables.
>
> How might I accomplish the same thing in PostgreSQL?

You can use a view

Re: Solution for Synonyms

From
Albe Laurenz
Date:
Thomas Kellerer wrote:
> mrprice22 wrote on 22.11.2013 19:25:
>> We are in the process of moving from Oracle to PostgreSQL.  We use a stored
>> procedure to populate some reporting tables once an hour.  There are two
>> sets of these tables, set A and set B.  We use synonyms to point to the
>> “active” set of tables at any given time.
>>
>> The procedure works like this:
>>
>> 1. If set A tables are “active” truncate set B tables or if set A tables are
>> “active” truncate set B tables.
>> 2. Populate set B tables.
>> 3. Set synonyms to point to set B tables.
>>
>> How might I accomplish the same thing in PostgreSQL?
> 
> You can use a view

You can also keep the tables A and B in different schemas
(probably a good idea anyway) and change search_path on the client side.

Yours,
Laurenz Albe