Created
October 6, 2017 19:02
-
-
Save JoshCheek/e19f83f271dc16d7825e2e4079538ba8 to your computer and use it in GitHub Desktop.
PostgreSQL cursor example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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"}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Excellent!!!!