How do you define postgres functions in Rails and

Question

I know nothing about Postgres functions however the need to use them has come up a couple of times recently (this may be indicative that I'm approaching the problem incorrectly but regardless...)

I would like to define a Postgres function like this which I will then access via queries in my Rails app:

CREATE FUNCTION date_in_time_zone(time_zone) RETURNS date AS 
  'SELECT DATE(NOW() AT TIME ZONE time_zone);' 
  LANGUAGE SQL; 

(from this question).

Question part 1: How do I define this function?

I presume I would do so as follows:

sql = "CREATE FUNCTION date_in_time_zone(time_zone) RETURNS date AS 
       'SELECT DATE(NOW() AT TIME ZONE time_zone);' 
       LANGUAGE SQL;"
ActiveRecord::Base.connection.execute sql

Is this correct?

Question part 2: What is the scope and lifetime of this function?

Once defined, how long does this function last for and what is the best way to manage versioning that function. If it is defined in the database rather than Rails then I will have to be careful to destroy it or update it if ever I want to change how it works. I will also need some way of initializing the function each time I load my Rails environment.

Should I create the function once on the initialization of the app or does it need to be created for each web request? I presume the former as it's being defined in the database but I'm not sure.

Question part 3: Should I even use this approach?

Is this just a bad idea? Should I be trying to solve the problem a different way? I really don't know what the scope or lifetime of these functions is.

Answer 1

Once created, a function in PostgreSQL is persisted permanently. Like other objects it lives inside a schema and is only found if this schema is in the search_path of your current session.

In a general-purpose database, you would create your functions in the default schema public. In a more sophisticated setup, you might have a dedicated schema for functions or a dedicated schema per user. The search_path would be set accordingly.

Use CREATE OR REPLACE FUNCTION ... instead of just CREATE FUNCTION ... so you can replace the body of an existing function (without changing IN or OUT types). More about this in this recent answer by @Pavel Stehule on dba.SE.

Be aware of the slightly different effects of time zone names and abbreviations or numeric offsets. Related questions / answers:
Time zone names with identical properties yield different result when applied to timestamp
PostgreSQL - how to render date in different time zone?