![]() ![]() Reverses the order of the characters in the string. replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef. Replaces all occurrences in string of substring from with substring to. Also I need to do that without stopping the database / on a working database. ![]() Which is more complicated than you'd think because you can write someview.* too.Īll in all - it's complicated, and nobody's wanted it enough to do the work to implement it. replace ( string text, from text, to text) text. I need to replace with but leave everything else untouched. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a * wildcard. Yet if you dropped and re-created view_A, the column would appear. So if you had view_A that does a SELECT * FROM view_B, and you added a column to view_B, it wouldn't appear in view_A. However, views created with SELECT * FROM wouldn't "inherit" the new column, because * gets expanded into a column-list during view creation, though. If pg_depend was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it. Again, I suspect that's down to whole-row references. You can also wrap these statements in a transaction. In order to do so, you can use the DROP TRIGGER and CREATE TRIGGER statements. It's less clear why adding a column is not permitted. PostgreSQL doesn’t support the OR REPLACE statement that allows you to modify the trigger definition like the function that will be executed when the trigger is fired. It'd also need to look for whole-row references and disallow changes in those cases. In oracle database following syntax create or replaces function functionname even if we only change the function signature.(i.e data type of function. To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It's theoretically possible, of course, but nobody's written the code to handle it yet. ![]()
0 Comments
Leave a Reply. |