Search code examples
sqlrubyfunctionsequel

How can I create SQL functions in Sequel?

I have a project with bunch of SQL scripts and I'm migrating the project into Sequel.

In the old scripts I define a fair number of SQL functions to build up my queries. How should I create these functions so that I can access them from Sequel?

E.g. I have a SQL function 

CREATE FUNCTION my_func(...) RETURNS integer AS $$
  SELECT ...
$$ LANGUAGE SQL;

Should I just enclose the above text in a string and call the following?

DB.run("CREATE FUNCTION my_func(...) RETURNS integer AS $$ 
          SELECT ... 
        $$ LANGUAGE SQL;")

Seems like there might be a better way.

I can re-write the function itself as a ruby function using Sequel, but I don't believe this will accomplish what I want because I will want to run queries that use the SQL queries in WHERE clauses and such.

Thanks!

Solution

  • Sequel actually supports creating PostgreSQL functions:

    DB.create_function('myfunc', 'SELECT ...', :args=>[:integer, :text], :returns=>:integer)

    However, I regret adding this and will probably move it to an extension in the next major release. Using DB.run for database specific stuff like this is the way to go IMO.