PRINT 'Testing stored procedures';

-- Test language if it does exist already
SET @A = SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql';
IF CAST (@A AS INTEGER) = 0
BEGIN
	-- Create language
	PRINT '  Creating language';
	CREATE LANGUAGE plpgsql;
END

PRINT '  Creating functions';
-- Create a function
CREATE FUNCTION a_function () RETURNS int4 AS '
DECLARE
	an_integer int4;
BEGIN
	an_integer := 10 * 10;
	return an_integer;
END;
' LANGUAGE 'plpgsql';
-- Create a function
CREATE FUNCTION add_one (integer) RETURNS INTEGER AS $$
BEGIN
	RETURN $1 + 1;
END;
$$ LANGUAGE 'plpgsql';
-- Create a function
CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS $A$
BEGIN
	RETURN $1 || $2;
END;
$A$ LANGUAGE 'plpgsql';

-- Test functions
PRINT '  Testing functions';
-- a_function
SET @A = SELECT a_function();
ASSERT @A[0][0] = 100;
SET @A = SELECT a_function() AS output;
ASSERT @A[0][0] = 100;
-- add_one
SET @A = SELECT add_one(10);
ASSERT @A[0][0] = 11;
SET @A = 10;
SET @B = SELECT add_one(@A) AS output;
ASSERT @B[0][0] = 11;
-- concat_text
SET @A = SELECT concat_text('abc', 'def');
ASSERT @A[0][0] = 'abcdef';
SET @A = 'abc', @B = 'def';
SET @C = SELECT concat_text('@A', '@B') AS output;
ASSERT @C[0][0] = 'abcdef';

-- Drop functions
PRINT '  Droping functions';
DROP FUNCTION a_function();
DROP FUNCTION add_one(integer);
DROP FUNCTION concat_text(text, text);
