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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With