Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 308
brahn Avatar asked Sep 13 '25 10:09

brahn


1 Answers

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.

like image 145
Jeremy Evans Avatar answered Sep 15 '25 00:09

Jeremy Evans