Solution for Synonyms

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general