Created
May 3, 2018 10:23
-
-
Save strk/cb40ad976742818deb91e67aeb54f49d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP FUNCTION IF EXISTS table_version.ver_unknown_revisions(); | |
| CREATE OR REPLACE FUNCTION table_version.ver_unknown_revisions() | |
| RETURNS TABLE (tab TEXT, rev INT) AS | |
| $$ | |
| DECLARE | |
| v_rec RECORD; | |
| BEGIN | |
| DROP TABLE IF EXISTS tmp_ids; | |
| CREATE TEMP TABLE tmp_ids(t text, r int); | |
| FOR v_rec IN SELECT schema_name, table_name | |
| FROM table_version.versioned_tables | |
| LOOP | |
| BEGIN | |
| EXECUTE format( | |
| 'INSERT INTO tmp_ids | |
| SELECT distinct ''%s_%s''::text as tab, _revision_expired rev_exp | |
| FROM table_version.%s_%s_revision | |
| WHERE _revision_created > _revision_expired' | |
| , | |
| v_rec.schema_name, v_rec.table_name, | |
| v_rec.schema_name, v_rec.table_name); | |
| EXCEPTION WHEN UNDEFINED_TABLE THEN | |
| RAISE WARNING 'Spurious record in table_version.versioned_tables ' | |
| 'for table % in schema %: % does not exist)', | |
| v_rec.table_name, v_rec.schema_name, | |
| format('table_version.%s_%s_revision', | |
| v_rec.schema_name, v_rec.table_name); | |
| END; | |
| END LOOP; | |
| RETURN QUERY SELECT distinct * FROM tmp_ids | |
| RETURN; | |
| END; | |
| $$ | |
| LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment