Update /contrib for "autocommit TO 'on'".

Create objects in public schema.

Make spacing/capitalization consistent.

Remove transaction block use for object creation.

Remove unneeded function GRANTs.

11 files changed, 105 insertions, 80 deletions

diff --git a/contrib/pgcrypto/sql/blowfish.sql b/contrib/pgcrypto/sql/blowfish.sql
index e1fa7b1f49..93187dcba3 100644
--- a/contrib/pgcrypto/sql/blowfish.sql
+++ b/contrib/pgcrypto/sql/blowfish.sql
@@ -2,50 +2,52 @@
 -- Blowfish cipher
 --
 
+SET autocommit TO 'on';
+
 -- some standard Blowfish testvalues
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('0000000000000000', 'hex'),
 decode('0000000000000000', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('ffffffffffffffff', 'hex'),
 decode('ffffffffffffffff', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('1000000000000001', 'hex'),
 decode('3000000000000000', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('1111111111111111', 'hex'),
 decode('1111111111111111', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('0123456789abcdef', 'hex'),
 decode('fedcba9876543210', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('01a1d6d039776742', 'hex'),
 decode('fedcba9876543210', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('ffffffffffffffff', 'hex'),
 decode('0000000000000000', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
 -- setkey
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('fedcba9876543210', 'hex'),
 decode('f0e1d2c3b4a5968778695a4b3c2d1e0f', 'hex'),
 'bf-ecb/pad:none'), 'hex');
 
 -- with padding
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('01234567890123456789', 'hex'),
 decode('33443344334433443344334433443344', 'hex'),
 'bf-ecb'), 'hex');
@@ -53,13 +55,13 @@ decode('33443344334433443344334433443344', 'hex'),
 -- cbc
 
 -- 28 bytes key
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('6b77b4d63006dee605b156e27403979358deb9e7154616d959f1652bd5', 'hex'),
 decode('37363534333231204e6f77206973207468652074696d6520666f7220', 'hex'),
 'bf-cbc'), 'hex');
 
 -- 29 bytes key
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('6b77b4d63006dee605b156e27403979358deb9e7154616d959f1652bd5ff92cc', 'hex'),
 decode('37363534333231204e6f77206973207468652074696d6520666f722000', 'hex'),
 'bf-cbc'), 'hex');
diff --git a/contrib/pgcrypto/sql/crypt-blowfish.sql b/contrib/pgcrypto/sql/crypt-blowfish.sql
index 6b82fdff63..5128675e0c 100644
--- a/contrib/pgcrypto/sql/crypt-blowfish.sql
+++ b/contrib/pgcrypto/sql/crypt-blowfish.sql
@@ -2,16 +2,19 @@
 -- crypt() and gen_salt(): bcrypt
 --
 
-select crypt('', '$2a$06$RQiOJ.3ELirrXwxIZY8q0O');
+SET autocommit TO 'on';
 
-select crypt('foox', '$2a$06$RQiOJ.3ELirrXwxIZY8q0O');
+SELECT crypt('', '$2a$06$RQiOJ.3ELirrXwxIZY8q0O');
 
-create table ctest (data text, res text, salt text);
-insert into ctest values ('password', '', '');
+SELECT crypt('foox', '$2a$06$RQiOJ.3ELirrXwxIZY8q0O');
 
-update ctest set salt = gen_salt('bf', 8);
-update ctest set res = crypt(data, salt);
-select res = crypt(data, res) as "worked" from ctest;
+CREATE TABLE ctest (data text, res text, salt text);
+INSERT INTO ctest VALUES ('password', '', '');
 
-drop table ctest;
+UPDATE ctest SET salt = gen_salt('bf', 8);
+UPDATE ctest SET res = crypt(data, salt);
+SELECT res = crypt(data, res) AS "worked" 
+FROM ctest;
+
+DROP TABLE ctest;
 
diff --git a/contrib/pgcrypto/sql/crypt-des.sql b/contrib/pgcrypto/sql/crypt-des.sql
index 2a2fbfb36c..ff43112dd8 100644
--- a/contrib/pgcrypto/sql/crypt-des.sql
+++ b/contrib/pgcrypto/sql/crypt-des.sql
@@ -2,16 +2,19 @@
 -- crypt() and gen_salt(): crypt-des
 --
 
-select crypt('', 'NB');
+SET autocommit TO 'on';
 
-select crypt('foox', 'NB');
+SELECT crypt('', 'NB');
 
-create table ctest (data text, res text, salt text);
-insert into ctest values ('password', '', '');
+SELECT crypt('foox', 'NB');
 
-update ctest set salt = gen_salt('des');
-update ctest set res = crypt(data, salt);
-select res = crypt(data, res) as "worked" from ctest;
+CREATE TABLE ctest (data text, res text, salt text);
+INSERT INTO ctest VALUES ('password', '', '');
 
-drop table ctest;
+UPDATE ctest SET salt = gen_salt('des');
+UPDATE ctest SET res = crypt(data, salt);
+SELECT res = crypt(data, res) AS "worked"
+FROM ctest;
+
+DROP TABLE ctest;
 
diff --git a/contrib/pgcrypto/sql/crypt-md5.sql b/contrib/pgcrypto/sql/crypt-md5.sql
index 8ef8dbae0f..c570e4ab7a 100644
--- a/contrib/pgcrypto/sql/crypt-md5.sql
+++ b/contrib/pgcrypto/sql/crypt-md5.sql
@@ -2,16 +2,19 @@
 -- crypt() and gen_salt(): md5
 --
 
-select crypt('', '$1$Szzz0yzz');
+SET autocommit TO 'on';
 
-select crypt('foox', '$1$Szzz0yzz');
+SELECT crypt('', '$1$Szzz0yzz');
 
-create table ctest (data text, res text, salt text);
-insert into ctest values ('password', '', '');
+SELECT crypt('foox', '$1$Szzz0yzz');
 
-update ctest set salt = gen_salt('md5');
-update ctest set res = crypt(data, salt);
-select res = crypt(data, res) as "worked" from ctest;
+CREATE TABLE ctest (data text, res text, salt text);
+INSERT INTO ctest VALUES ('password', '', '');
 
-drop table ctest;
+UPDATE ctest SET salt = gen_salt('md5');
+UPDATE ctest SET res = crypt(data, salt);
+SELECT res = crypt(data, res) AS "worked"
+FROM ctest;
+
+DROP TABLE ctest;
 
diff --git a/contrib/pgcrypto/sql/crypt-xdes.sql b/contrib/pgcrypto/sql/crypt-xdes.sql
index 6fd85b929a..c6205ab600 100644
--- a/contrib/pgcrypto/sql/crypt-xdes.sql
+++ b/contrib/pgcrypto/sql/crypt-xdes.sql
@@ -2,16 +2,19 @@
 -- crypt() and gen_salt(): extended des
 --
 
-select crypt('', '_J9..j2zz');
+SET autocommit TO 'on';
 
-select crypt('foox', '_J9..j2zz');
+SELECT crypt('', '_J9..j2zz');
 
-create table ctest (data text, res text, salt text);
-insert into ctest values ('password', '', '');
+SELECT crypt('foox', '_J9..j2zz');
 
-update ctest set salt = gen_salt('xdes', 1001);
-update ctest set res = crypt(data, salt);
-select res = crypt(data, res) as "worked" from ctest;
+CREATE TABLE ctest (data text, res text, salt text);
+INSERT INTO ctest VALUES ('password', '', '');
 
-drop table ctest;
+UPDATE ctest SET salt = gen_salt('xdes', 1001);
+UPDATE ctest SET res = crypt(data, salt);
+SELECT res = crypt(data, res) AS "worked"
+FROM ctest;
+
+DROP TABLE ctest;
 
diff --git a/contrib/pgcrypto/sql/hmac-md5.sql b/contrib/pgcrypto/sql/hmac-md5.sql
index d3cd1f649d..ed2b65b7b8 100644
--- a/contrib/pgcrypto/sql/hmac-md5.sql
+++ b/contrib/pgcrypto/sql/hmac-md5.sql
@@ -2,43 +2,45 @@
 -- HMAC-MD5
 --
 
-select encode(hmac(
+SET autocommit TO 'on';
+
+SELECT encode(hmac(
 'Hi There',
 decode('0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b', 'hex'),
 'md5'), 'hex');
 
 -- 2
-select encode(hmac(
+SELECT encode(hmac(
 'Jefe',
 'what do ya want for nothing?',
 'md5'), 'hex');
 
 -- 3
-select encode(hmac(
+SELECT encode(hmac(
 decode('dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', 'hex'),
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'md5'), 'hex');
 
 -- 4
-select encode(hmac(
+SELECT encode(hmac(
 decode('cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd', 'hex'),
 decode('0102030405060708090a0b0c0d0e0f10111213141516171819', 'hex'),
 'md5'), 'hex');
 
 -- 5
-select encode(hmac(
+SELECT encode(hmac(
 'Test With Truncation',
 decode('0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c', 'hex'),
 'md5'), 'hex');
 
 -- 6
-select encode(hmac(
+SELECT encode(hmac(
 'Test Using Larger Than Block-Size Key - Hash Key First',
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'md5'), 'hex');
 
 -- 7
-select encode(hmac(
+SELECT encode(hmac(
 'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data',
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'md5'), 'hex');
diff --git a/contrib/pgcrypto/sql/hmac-sha1.sql b/contrib/pgcrypto/sql/hmac-sha1.sql
index f08c438963..fb70424dce 100644
--- a/contrib/pgcrypto/sql/hmac-sha1.sql
+++ b/contrib/pgcrypto/sql/hmac-sha1.sql
@@ -2,43 +2,45 @@
 -- HMAC-MD5
 --
 
-select encode(hmac(
+SET autocommit TO 'on';
+
+SELECT encode(hmac(
 'Hi There',
 decode('0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b', 'hex'),
 'sha1'), 'hex');
 
 -- 2
-select encode(hmac(
+SELECT encode(hmac(
 'Jefe',
 'what do ya want for nothing?',
 'sha1'), 'hex');
 
 -- 3
-select encode(hmac(
+SELECT encode(hmac(
 decode('dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', 'hex'),
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'sha1'), 'hex');
 
 -- 4
-select encode(hmac(
+SELECT encode(hmac(
 decode('cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd', 'hex'),
 decode('0102030405060708090a0b0c0d0e0f10111213141516171819', 'hex'),
 'sha1'), 'hex');
 
 -- 5
-select encode(hmac(
+SELECT encode(hmac(
 'Test With Truncation',
 decode('0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c', 'hex'),
 'sha1'), 'hex');
 
 -- 6
-select encode(hmac(
+SELECT encode(hmac(
 'Test Using Larger Than Block-Size Key - Hash Key First',
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'sha1'), 'hex');
 
 -- 7
-select encode(hmac(
+SELECT encode(hmac(
 'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data',
 decode('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'hex'),
 'sha1'), 'hex');
diff --git a/contrib/pgcrypto/sql/init.sql b/contrib/pgcrypto/sql/init.sql
index 8bddb73773..08ffeb4981 100644
--- a/contrib/pgcrypto/sql/init.sql
+++ b/contrib/pgcrypto/sql/init.sql
@@ -3,10 +3,11 @@
 --
 
 \set ECHO none
+SET autocommit TO 'on';
 \i pgcrypto.sql
 \set ECHO all
 
 -- check for encoding fn's
-select encode('foo', 'hex');
-select decode('666f6f', 'hex');
+SELECT encode('foo', 'hex');
+SELECT decode('666f6f', 'hex');
 
diff --git a/contrib/pgcrypto/sql/md5.sql b/contrib/pgcrypto/sql/md5.sql
index 78e4cfee0f..0c851217b2 100644
--- a/contrib/pgcrypto/sql/md5.sql
+++ b/contrib/pgcrypto/sql/md5.sql
@@ -2,11 +2,13 @@
 -- MD5 message digest
 --
 
-select encode(digest('', 'md5'), 'hex');
-select encode(digest('a', 'md5'), 'hex');
-select encode(digest('abc', 'md5'), 'hex');
-select encode(digest('message digest', 'md5'), 'hex');
-select encode(digest('abcdefghijklmnopqrstuvwxyz', 'md5'), 'hex');
-select encode(digest('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 'md5'), 'hex');
-select encode(digest('12345678901234567890123456789012345678901234567890123456789012345678901234567890', 'md5'), 'hex');
+SET autocommit TO 'on';
+
+SELECT encode(digest('', 'md5'), 'hex');
+SELECT encode(digest('a', 'md5'), 'hex');
+SELECT encode(digest('abc', 'md5'), 'hex');
+SELECT encode(digest('message digest', 'md5'), 'hex');
+SELECT encode(digest('abcdefghijklmnopqrstuvwxyz', 'md5'), 'hex');
+SELECT encode(digest('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 'md5'), 'hex');
+SELECT encode(digest('12345678901234567890123456789012345678901234567890123456789012345678901234567890', 'md5'), 'hex');
 
diff --git a/contrib/pgcrypto/sql/rijndael.sql b/contrib/pgcrypto/sql/rijndael.sql
index fbacdc6dfc..793b07f362 100644
--- a/contrib/pgcrypto/sql/rijndael.sql
+++ b/contrib/pgcrypto/sql/rijndael.sql
@@ -2,41 +2,43 @@
 -- AES / Rijndael-128 cipher
 --
 
+SET autocommit TO 'on';
+
 -- some standard Rijndael testvalues
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('00112233445566778899aabbccddeeff', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f', 'hex'),
 'aes-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('00112233445566778899aabbccddeeff', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f1011121314151617', 'hex'),
 'aes-ecb/pad:none'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('00112233445566778899aabbccddeeff', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', 'hex'),
 'aes-ecb/pad:none'), 'hex');
 
 -- cbc
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('00112233445566778899aabbccddeeff', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', 'hex'),
 'aes-cbc/pad:none'), 'hex');
 
 -- key padding
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('0011223344', 'hex'),
 decode('000102030405', 'hex'),
 'aes-cbc'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('0011223344', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f10111213', 'hex'),
 'aes-cbc'), 'hex');
 
-select encode(encrypt(
+SELECT encode(encrypt(
 decode('0011223344', 'hex'),
 decode('000102030405060708090a0b0c0d0e0f101112131415161718191a1b', 'hex'),
 'aes-cbc'), 'hex');
diff --git a/contrib/pgcrypto/sql/sha1.sql b/contrib/pgcrypto/sql/sha1.sql
index 11ee5426cb..1c0fcd9072 100644
--- a/contrib/pgcrypto/sql/sha1.sql
+++ b/contrib/pgcrypto/sql/sha1.sql
@@ -2,11 +2,13 @@
 -- SHA1 message digest
 --
 
-select encode(digest('', 'sha1'), 'hex');
-select encode(digest('a', 'sha1'), 'hex');
-select encode(digest('abc', 'sha1'), 'hex');
-select encode(digest('message digest', 'sha1'), 'hex');
-select encode(digest('abcdefghijklmnopqrstuvwxyz', 'sha1'), 'hex');
-select encode(digest('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 'sha1'), 'hex');
-select encode(digest('12345678901234567890123456789012345678901234567890123456789012345678901234567890', 'sha1'), 'hex');
+SET autocommit TO 'on';
+
+SELECT encode(digest('', 'sha1'), 'hex');
+SELECT encode(digest('a', 'sha1'), 'hex');
+SELECT encode(digest('abc', 'sha1'), 'hex');
+SELECT encode(digest('message digest', 'sha1'), 'hex');
+SELECT encode(digest('abcdefghijklmnopqrstuvwxyz', 'sha1'), 'hex');
+SELECT encode(digest('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 'sha1'), 'hex');
+SELECT encode(digest('12345678901234567890123456789012345678901234567890123456789012345678901234567890', 'sha1'), 'hex');