PostgreSQL cursor example

pg_cursor_example.rb

require 'pg'
db = PG.connect dbname: 'postgres'
db.exec("DROP DATABASE IF EXISTS just_fkn_around;")
db.exec("CREATE DATABASE just_fkn_around;")
db = PG.connect dbname: 'just_fkn_around'

define_method(:sql) { |sql| db.exec(sql).to_a }

sql <<-SQL
-- some data to query
CREATE TABLE strings (
  id serial primary key,
  val varchar
);
INSERT INTO strings (val)
VALUES ('a'), ('b'), ('c');

-- iterate over the data in a cursor
CREATE OR REPLACE FUNCTION josh_test()
RETURNS SETOF varchar AS $$
DECLARE
  curs CURSOR FOR SELECT * FROM strings;
  row  RECORD;
BEGIN
  open curs;
  LOOP
    FETCH FROM curs INTO row;
    EXIT WHEN NOT FOUND;
    return next row.val;
  END LOOP;
  
  -- RAISE NOTICE '%', val;
END; $$ LANGUAGE plpgsql;

SELECT josh_test();
SQL
# => [{"josh_test"=>"a"}, {"josh_test"=>"b"}, {"josh_test"=>"c"}]