Changeset 14

Timestamp:

08/25/08 18:53:24 (4 years ago)

Author:

fumanchu

Message:

New 'global' function to fake passing records to functions. Needs tests. Also added an assert_values function (also needs tests), and made assert_rows take FROM clauses.

Location:

trunk/epic

Files:

• epic.sql (modified) (5 diffs)
• test/test_asserts.sql (modified) (2 diffs)

trunk/epic/epic.sql

@@ -211 +211 @@
 
 
+-------------------------------- global records --------------------------------
+
+
+CREATE OR REPLACE FUNCTION _ensure_globals() RETURNS boolean AS $$
+BEGIN
+  SET client_min_messages = warning;
+  
+  BEGIN
+    CREATE SEQUENCE _global_ids;
+  EXCEPTION WHEN duplicate_table THEN
+    NULL;
+  END;
+  
+  RETURN TRUE;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM _ensure_globals();
+DROP FUNCTION _ensure_globals();
+
+
+CREATE OR REPLACE FUNCTION global(call text) RETURNS record AS $$
+-- Stores the given call's output in a TEMP table, and returns it as a record.
+-- 
+-- 'call' can be any table, view, or procedure that returns records.
+-- 
+-- The returned record includes the following additional attributes:
+--   * tablename (text): The complete name of the TEMP table. This allows you
+--       to pass my_record_var.tablename to functions that take a 'call text'
+--       argument, such as assert_column, assert_values, and assert_empty
+--       (since no procedural languages support passing records as args).
+DECLARE
+  tablename      text;
+  result         record;
+BEGIN
+  tablename := '_global_' || nextval('_global_ids');
+  EXECUTE 'CREATE TEMP TABLE ' || tablename || ' AS SELECT * FROM ' || call;
+  EXECUTE 'SELECT ''' || tablename || '''::text AS tablename, * FROM ' || tablename INTO result;
+  RETURN result;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION attributes(tablename text) RETURNS SETOF pg_attribute AS $$
+DECLARE
+  rec      record;
+BEGIN
+  FOR rec IN
+    SELECT * FROM pg_attribute
+    WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = tablename)
+    -- Exclude system columns
+    AND attnum >= 1
+  LOOP
+    RETURN NEXT rec;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
 ------------------------------ Runners ------------------------------
 
@@ -484 +542 @@
 -- Example:
 --
---    PERFORM test.assert_raises('get_transaction_by_id("a")', 'Bad argument', NULL);
+--    PERFORM test.assert_raises('get_transaction_by_id(''a'')', 'Bad argument', NULL);
 --
 -- If errm or state are NULL, that value will not be tested. This allows
@@ -527 +585 @@
 --
 -- Both arguments should be SELECT statements yielding a single row or a set of rows.
--- It is common for the 'expected' arg to be sans a FROM clause, and simply SELECT values.
+-- Either may also be any table, view, or procedure call that returns records.
+-- It is also common for the 'expected' arg to be sans a FROM clause, and simply SELECT values.
 -- Neither source nor expected need to be sorted. Either may include a trailing semicolon.
 --
@@ -536 +595 @@
 DECLARE
   rec     record;
-BEGIN
-  FOR rec in EXECUTE rtrim(source, ';') || ' EXCEPT ' || rtrim(expected, ';')
+  s       text;
+  e       text;
+BEGIN
+  s := rtrim(source, ';');
+  IF NOT s ILIKE 'SELECT%' THEN
+    s := 'SELECT * FROM ' || s;
+  END IF;
+  
+  e := rtrim(expected, ';');
+  IF NOT e ILIKE 'SELECT%' THEN
+    e := 'SELECT * FROM ' || e;
+  END IF;
+  
+  FOR rec in EXECUTE s || ' EXCEPT ' || e
   LOOP
     RAISE EXCEPTION 'Record: % from: % not found in: %', rec, source, expected;
   END LOOP;
   
-  FOR rec in EXECUTE rtrim(expected, ';') || ' EXCEPT ' || rtrim(source, ';')
+  FOR rec in EXECUTE e || ' EXCEPT ' || s
   LOOP
     RAISE EXCEPTION 'Record: % from: % not found in: %', rec, expected, source;
@@ -634 +705 @@
 
 
+CREATE OR REPLACE FUNCTION test.assert_values(call_1 text, call_2 text, columns text) RETURNS VOID AS $$
+-- Raises an exception if SELECT columns FROM call_1 != SELECT columns FROM call_2.
+--
+-- Example:
+--    row_1 := obj('get_favorite_user_ids(' || user_id || ')')
+--    PERFORM test.assert_equal(row_1.object, 'users WHERE user_id = 355', 'last_name');
+--
+BEGIN
+  PERFORM test.assert_rows(
+    'SELECT ' || columns || ' FROM ' || call_1,
+    'SELECT ' || columns || ' FROM ' || call_2
+    );
+END;
+$$ LANGUAGE plpgsql;
+
+
 CREATE OR REPLACE FUNCTION test.assert_empty(tablenames text[]) RETURNS VOID AS $$
 -- Raises an exception if the given tables have any rows.

trunk/epic/test/test_asserts.sql

@@ -315 +315 @@
     'SELECT tablename FROM pg_tables;',
     'SELECT relname FROM pg_class where relkind = ''r''');
+  -- SELECT-less argument
+  PERFORM test.assert_rows(
+    'SELECT adrelid, adnum, adbin, adsrc FROM pg_attrdef',
+    'pg_attrdef');
   
   -- ...and an assertion that should fail
@@ -320 +324 @@
   BEGIN
     PERFORM test.assert_rows(
-      'SELECT * FROM generate_series(1, 10)', 
+      'generate_series(1, 10)', 
       'SELECT * FROM generate_series(1, 5)');
   EXCEPTION WHEN OTHERS THEN
     failed := true;
-    IF SQLERRM = 'Record: (6) from: SELECT * FROM generate_series(1, 10) not found in: SELECT * FROM generate_series(1, 5)' THEN
+    IF SQLERRM = 'Record: (6) from: generate_series(1, 10) not found in: SELECT * FROM generate_series(1, 5)' THEN
       NULL;
     ELSE