4

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!

brahn
  • 12,096
  • 11
  • 39
  • 49

2 Answers2

5

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.

Jeremy Evans
  • 11,959
  • 27
  • 26
  • Thanks Jeremy! Ooc, why do you regret adding it? – brahn Dec 12 '12 at 21:16
  • For context, part of my interest is in being able to easily modify a function's definition by simply re-defining it, and `CREATE OR REPLACE FUNCTION` only works if one isn't changing input or output params. So I'm trying to get closer to a ruby-esque function definition method wherein an old definition gets automatically overwritten regardless of types. – brahn Dec 12 '12 at 21:19
  • There's a lot of complexity in the code due to all of the options it supports. Unless you are metaprogramming your database functions, there's not much value to abstracting the interface, and few people do that. – Jeremy Evans Dec 14 '12 at 00:19
  • Any change happened on this? :) – bbozo May 17 '17 at 10:20
-1

As you have tagged ruby in your question, to store your SQL calls you can use this very useful GEM:

https://github.com/sufleR/sql_query

bonafernando
  • 1,048
  • 12
  • 14
  • 1
    It is also tagged `Sequel` - the question is how to write sql functions in sequel, not just in ruby. – knut Aug 31 '15 at 20:37