SQL -=- Records to CSV SQL function

Hi!

If you want to convert an SQL resultset to a CSV string, your can use the following function.
  
Function

CREATE OR REPLACE FUNCTION myschema.to_csv(query_to_convert character varying, separator character varying)
  RETURNS text AS
$BODY$
declare
    r RECORD;
    t character varying;
begin
    t := '';
    
    FOR r IN EXECUTE query_to_convert LOOP
        t := t || r.data || separator;
    END LOOP;

    -- We removes the trailing comma
    RETURN substring(t, 0, char_length(t)-1) ;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Function Call

SELECT myschema.to_csv('SELECT mycol as data  FROM myschema.mytable;',','); 

This will return a varchar containing all the records reparated with the specified delimiter, in our example a comma.

Ruby on Rails -=- EZ_Where plugin

Here's a good plugin used to build where conditions for Rails without SQL. It is called ez_where plugin for rails.

You can download it at https://rubyforge.org/projects/ez-where/

Here's the description of the project as shown on one of the creator's blog (see: http://brainspl.at/articles/tag/conditions)

This plugin is meant to be used as a nice ruby like syntax for creating the :conditions part of an ActiveRecord::Base.find. We also add the ActiveRecord::Base.ez_find method. This method takes a block to simplify single and multi table queries.

articles = Article.ez_find(:all, :include => :author) do |article, author|
  article.title =~ "%Foo Title%"
  author.any do
    name == 'Ezra'
    name == 'Fab'
  end 
end

This will produce :conditions => ["article.title LIKE ? AND
(authors.name = ? OR authors.name = ?)",
"%Foo Title%", "Ezra", "Fab"]

Basically here is the breakdown of how we map ruby operators
to SQL operators:

foo == 'bar'           #=> ["foo = ?", 'bar']
foo =~ '%bar'          #=> ["foo LIKE ?", '%bar']
foo <=> (1..5)         #=> ["foo BETWEEN ? AND ?", 1, 5]
id === [1, 2, 3, 5, 8] #=> ["id IN(?)", [1, 2, 3, 5, 8]]
<, >, >=, <= et all will just work like you expect.

There is also the ability to create the conditions in stages so
you can build up a query:

cond = Caboose::EZ::Condition.new do
  foo == 'bar'
  baz <=> (1..5)
  id === [1, 2, 3, 5, 8]
end
 
@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["foo = ? AND baz BETWEEN ? AND ? AND id IN (?)",
"bar", 1, 5, [1, 2, 3, 5, 8]]


You can even do nested sub conditions. condition will use AND
by default in the sub condition:

cond = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  baz <=> (1..5)
  id === [1, 2, 3, 5, 8]
  condition :my_other_table do
    fiz =~ '%faz%'
  end
end

@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["my_table.foo = ? AND my_table.baz BETWEEN ? AND ?
AND my_table.id IN (?) AND (my_other_table.fiz LIKE ?)",
"bar", 1, 5, [1, 2, 3, 5, 8], "%faz%"]

You can also build multiple Condition objects and join
them together for one monster find:

cond_a = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  condition :my_other_table do
    id === [1, 3, 8]
    foo == 'other bar'
    fiz =~ '%faz%'
  end
end

#=> ["my_table.foo = ? AND (my_other_table.id IN (?) AND my_other_table.foo = ?
AND my_other_table.fiz LIKE ?)", "bar", [1, 3, 8], "other bar", "%faz%"]

cond_b = Caboose::EZ::Condition.new :my_table do
  active == true
  archived == false
end

#=> ["my_table.active = ? AND my_table.archived = ?", true, false]

composed_cond = Caboose::EZ::Condition.new
composed_cond << cond_a
composed_cond << cond_b
composed_cond << 'fuzz IS NULL'

@result = Model.find(:all, :conditions => composed_cond.to_sql)

#=> ["(my_table.foo = ? AND (my_other_table.id IN (?) AND my_other_table.foo = ?
AND my_other_table.fiz LIKE ?)) AND (my_table.active = ? AND my_table.archived = ?)
AND fuzz IS NULL", "bar", [1, 3, 8], "other bar", "%faz%", true, false]

You can compose a new condition from different sources:

ar_instance = Author.find(1)

other_cond = Caboose::EZ::Condition.new :my_table do 
  foo == 'bar'; baz == 'buzz'
end

cond = Caboose::EZ::Condition.new
# another Condition
cond.append other_cond
# an array in AR condition format
cond.append ['baz = ? AND bar IS NOT NULL', 'fuzz'], :or
# a raw SQL string
cond.append 'biz IS NULL'
# an Active Record instance from DB or as Value Object
cond.append ar_instance

#(append is aliased to << because of syntax issues 
involving multiple args like :or)

@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["(my_table.foo = ? AND my_table.baz = ?) OR (baz = ? AND bar IS NOT NULL)
AND biz IS NULL AND authors.id = ?", "bar", "buzz", "fuzz", 1]

OK there is also other options for doing subconditions. OR is
aliased to any and any creates a subcondition that uses OR to
join the sub conditions:

cond = Caboose::EZ::Condition.new :my_table do
  foo == 'bar'
  any :my_other_table do
    baz === ['fizz', 'fuzz']
    biz == 'boz'
  end
end

@result = Model.find(:all, :conditions=> cond.to_sql)

#=> ["my_table.foo = ? AND (my_other_table.baz IN (?)
OR my_other_table.biz = ?)",
"bar", ["fizz", "fuzz"], "boz"]

OK lets look a bit more at ez_find with a few more complex queries:

# all articles written by Ezra. Here you can use a normal AR object
# in the conditions
# session[:user_id] = 2
ezra = Author.find(session[:user_id])    
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author << ezra # use AR instance to add condition; uses PK value if set: author.id = ezra.id
end 

#=>["(authors.id = ?)", 2]

# all articles written by Ezra, where he himself responds in comments
@articles = Article.ez_find(:all, :include => [:author, :comments]) do |article, author, comment|
  article.author_id == ezra.id
  comment.author_id == ezra.id   
end

#=>["(articles.author_id = ?) AND (comments.author_id = ?)", 2, 2]

# any articles written by Fab or Ezra
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author.name === ['Fab', 'Ezra']   
end

#=>["(authors.name IN (?))", ["Fab", "Ezra"]]

# any articles written by Fab or Ezra, using subcondition
@articles = Article.ez_find(:all, :include => :author) do |article, author|
  author.any do
    name == 'Ezra'
    name == 'Fab'
  end  
end

#=>["(authors.name = ? OR authors.name = ?)", "Ezra", "Fab"]

# any articles written by or commented on by Fab, using subcondition
@articles = Article.ez_find(:all, :include => [:author, :comments]) do |article, author, comment|
  article.sub { author_id == 1 }
  comment.outer = :or # set :outer for the comment condition, since it defaults to :and
  comment.sub { author_id == 1 }       
end

#=>["(articles.author_id = ?) OR (comments.author_id = ?)", 1, 1]

@articles = Article.ez_find(:all, :include => [:author, :comments],
                           :outer => { :comments => :or }, 
                           :inner => { :article => :or}) do |article, author, comment|
  article.sub { author_id == 1; author_id == 2 }
  comment.sub { author_id == 1 } 
end

["(articles.author_id = ? OR articles.author_id = ?) OR (comments.author_id = ?)", 1, 2, 1]

And finally you can use any and all with ez_condition like this:

cond = Article.ez_condition { active == true; archived == false }
cond.all { body =~ '%intro%'; body =~ '%demo%' }
cond.any { title =~ '%article%'; title =~ '%first%' }

#=> ["articles.active = ? AND articles.archived = ?
AND (articles.body LIKE ? AND articles.body LIKE ?)
AND (articles.title LIKE ? OR articles.title LIKE ?)",
true, false, "%intro%", "%demo%", "%article%", "%first%"]