How do you define postgres functions in Rails and what is their scope and lifetime?

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.

Erwin Brandstetter · Accepted Answer · 2022-05-16T20:58:21.810

Once created, a function in PostgreSQL is persisted permanently. Like other objects it lives inside a schema. Unless you schema-qualify the function name, it is only found if this schema is in the search_path of your current session (and no hidden by another function of the same in a different schema).

In a general-purpose database, you would create your functions in the default schema public, which is in the search path by default. 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 parameters). Recent related answer by @Pavel Stehule on dba.SE.

Be aware of the slightly different effects of time zone names, abbreviations, and numeric offsets. Related:

Do you recommend using Postgres functions in addition to Rails methods? I am aware that I could back into a bad alley here. In your experience *should* these types of problems be soluble without having to create database functions or do you unavoidably have to go deeper? — Peter Nixey, Dec 01 '13 at 17:39
I am not an expert with Postgres, not so much with Rails. The answer to your additional question very much depends on lots of details. — Erwin Brandstetter, Dec 01 '13 at 18:58

How do you define postgres functions in Rails and what is their scope and lifetime?

Question part 1: How do I define this function?

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

Question part 3: Should I even use this approach?

1 Answers1

Linked