Home Postgres PL/pgSQL function to apply series of changes to a table
Reply: 0

Postgres PL/pgSQL function to apply series of changes to a table

idazuwaika
1#
idazuwaika Published in 2017-12-07 14:56:56Z

I am looking to apply a data versioning approach to a customer profile table. In this case, the table will be created in a schema called profile. The main table is called profile_v0, which is the initial state of the customer profile, and have a primary key called name. From time to time, where will be changes captured as table change_log_1, change_log_2 and so on.

Thus, the latest customer profile is actually profile_v0 with changes applied sequentially from change_log_1 to the latest change log. The change log can overwrite a previous record with the same primary key, or can add new record.

How do I write the PL/pgSQL function to achieve above ? I'm imagining the function will only take the schema name as parameter, and will identifies all the tables residing in it and act accordingly.

To explain with example.

## profile.profile_v0

name, industry
Ford, Automotive
KFC, Food
7 Eleven, Retail
Google, Hardware
Microsoft, Software

...

## profile.change_log_1

name, industry
Google, Advertising
IBM, Services

...

## profile.change_log_2

name, industry
Cisco, Technology
KFC, Retail

At this point, the latest customer profile (could be a materialized view in different schema) should be:

name, industry
Ford, Automotive
7 Eleven, Retail
Microsoft, Software
Google, Advertising
IBM, Services
Cisco, Technology
KFC, Retail

Could this be implemented without PL/pgSQL? Note that the number of change logs will keep to increase.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299135 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO