4 ms·

Absolutely! We use rails. I just mentioned doing it in that method as opposed to throwing it in a migration. That migration shouldn't change in version control.

Absolutely! We use rails. I just mentioned doing it in that method as opposed to throwing it in a migration. That migration shouldn't change in version control. We wanted a history of the changes and an easy way to access it in migrations. Sorry for not explaining that fully.

rpeden 10y ago
Makes sense. I suppose you could also execute "REPLACE FUNCTION" and redefine the inside a new migration each time you want to change it. The downside is that you end up with the whole function in new a migration every time you want to change if. But if you're not updating your stored procedures very frequently (which is usually the case, IME), it probably wouldn't be too bad.
- jeffasinger 10y ago
  At work we use a rather idiosyncratic tool called RoundhousE to deploy our SQL. It treats sql labelled as a stored procedure differently, and will run the file on every deploy, as opposed to scripts that alter the schema, which are only ran once, and may not be modified. The system works pretty well.
  - slagfart 10y ago
    You don't need a tool for this. If you store base data in one schema, and functions/views in another, you can update this second schema willy nilly without worrying about damaging your data. Just start the second file with DROP SCHEMA IF EXISTS schemaname CASCADE; CREATE SCHEMA schemaname;. Then, make sure any functions/views/permissions built on top of this schema are also contained within this file.
    - inopinatus 10y ago
      Or you can just pull the SQL for the stored procedure from a table column and execute it dynamically. Now it's an ops problem :-)
      - jessaustin 10y ago
        Haha, usually we hear this from smart guys who have been working for about 8 months...
    - angersock 10y ago
      Oh, that's slick. Nice!
- inopinatus 10y ago
  Or you can just define the function every time you use it. https://www.postgresql.org/docs/current/static/sql-do.html https://www.postgresql.org/docs/current/static/sql-do.html
  - majewsky 10y ago
    That won't help if you want to use it for indexing, as the article demonstrated.

rpeden 10y ago

Makes sense. I suppose you could also execute "REPLACE FUNCTION" and redefine the inside a new migration each time you want to change it. The downside is that you end up with the whole function in new a migration every time you want to change if. But if you're not updating your stored procedures very frequently (which is usually the case, IME), it probably wouldn't be too bad.

jeffasinger 10y ago
At work we use a rather idiosyncratic tool called RoundhousE to deploy our SQL. It treats sql labelled as a stored procedure differently, and will run the file on every deploy, as opposed to scripts that alter the schema, which are only ran once, and may not be modified. The system works pretty well.
- slagfart 10y ago
  You don't need a tool for this. If you store base data in one schema, and functions/views in another, you can update this second schema willy nilly without worrying about damaging your data. Just start the second file with DROP SCHEMA IF EXISTS schemaname CASCADE; CREATE SCHEMA schemaname;. Then, make sure any functions/views/permissions built on top of this schema are also contained within this file.
  - inopinatus 10y ago
    Or you can just pull the SQL for the stored procedure from a table column and execute it dynamically. Now it's an ops problem :-)
    - jessaustin 10y ago
      Haha, usually we hear this from smart guys who have been working for about 8 months...
  - angersock 10y ago
    Oh, that's slick. Nice!
inopinatus 10y ago
Or you can just define the function every time you use it. https://www.postgresql.org/docs/current/static/sql-do.html https://www.postgresql.org/docs/current/static/sql-do.html
- majewsky 10y ago
  That won't help if you want to use it for indexing, as the article demonstrated.