Обсуждение: Global Variables?

When writing unit tests it's sometimes useful to stub functions such as
the current date and time

-- define mock functions
CREATE OR REPLACE FUNCTION _now() RETURNS timestamp with time zone AS $$
  BEGIN RETURN '2011-10-10 10:00'; END;
$$ LANGUAGE plpgsql;

-- define tables "accounts"
CREATE TABLE accounts (username varchar, expiration timestamp);

-- populate with sample data
COPY accounts FROM '/home/eradman/sample_accounts.txt';

-- define view "expired_accounts"
CREATE OR REPLACE VIEW expired_accounts AS SELECT * FROM accounts WHERE expiration < _now();

-- test views
SELECT assert(0, (SELECT count(*) FROM expired_accounts)::integer);

Is it possible to declare a global variable that can be referenced from
the user-defined function _now()? I'm looking for a means of abstraction
that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before
each assert()

current_time := '2012-01-01'::timestamp
SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer);

--
Eric Radman  |  http://eradman.com

On 11 October 2011 16:06, Eric Radman <ericshane@eradman.com> wrote:
> When writing unit tests it's sometimes useful to stub functions such as
> the current date and time

> Is it possible to declare a global variable that can be referenced from
> the user-defined function _now()? I'm looking for a means of abstraction
> that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before
> each assert()
>
> current_time := '2012-01-01'::timestamp
> SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer);

You could crate a table for such "constants" and read your
current-time from that table.

Additionally, I would put such stub functions in a separate schema and
create a test role with that schema as the top of their search_path.

That way, you could even override system function implementations (and
other definitions) and only have them apply to the role you're using
for unit testing.

CREATE ROLE unit_tester;
CREATE SCHEMA unit_tests AUTHORIZATION unit_tester;
SET search_path TO unit_tests, my_schema, public;

CREATE TABLE unit_test_parameters (
   current_time timestamp without time zone NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION now() RETURNS timestamp without time zone AS $$
SELECT current_time FROM unit_test_parameters LIMIT 1;
$$ LANGUAGE SQL

...etc...

UPDATE unit_test_parameters SET current_time = '2012-01-01'::timestamp;
SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer);

I'm not sure how you planned to use that _now() function with the
assert; I expected a WHERE clause in that query, but it isn't there.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

It would be interesting if the parameters/settings framework could be extended to provide
session/table/user/database level custom settings, accessible via the SET/SHOW/RESET commands.
Is there anything like this ever been considered/discussed ?

Στις Tuesday 11 October 2011 17:06:50 ο/η Eric Radman έγραψε:
> When writing unit tests it's sometimes useful to stub functions such as
> the current date and time
>
> -- define mock functions
> CREATE OR REPLACE FUNCTION _now() RETURNS timestamp with time zone AS $$
>   BEGIN RETURN '2011-10-10 10:00'; END;
> $$ LANGUAGE plpgsql;
>
> -- define tables "accounts"
> CREATE TABLE accounts (username varchar, expiration timestamp);
>
> -- populate with sample data
> COPY accounts FROM '/home/eradman/sample_accounts.txt';
>
> -- define view "expired_accounts"
> CREATE OR REPLACE VIEW expired_accounts AS SELECT * FROM accounts WHERE expiration < _now();
>
> -- test views
> SELECT assert(0, (SELECT count(*) FROM expired_accounts)::integer);
>
> Is it possible to declare a global variable that can be referenced from
> the user-defined function _now()? I'm looking for a means of abstraction
> that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before
> each assert()
>
> current_time := '2012-01-01'::timestamp
> SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer);
>
> --
> Eric Radman  |  http://eradman.com
>



--
Achilleas Mantzios

On Tue, Oct 11, 2011 at 04:26:47PM +0200, Alban Hertroys wrote:
> On 11 October 2011 16:06, Eric Radman <ericshane@eradman.com> wrote:
> > When writing unit tests it's sometimes useful to stub functions such
> > as the current date and time
>
> You could create a table for such "constants" and read your
> current-time from that table.
>
> Additionally, I would put such stub functions in a separate schema and
> create a test role with that schema as the top of their search_path.
>
> That way, you could even override system function implementations (and
> other definitions) and only have them apply to the role you're using
> for unit testing.

> CREATE ROLE unit_tester;
> CREATE SCHEMA unit_tests AUTHORIZATION unit_tester;
> SET search_path TO unit_tests, my_schema, public;
>
> CREATE TABLE unit_test_parameters (
>    current_time timestamp without time zone NOT NULL DEFAULT now()
> );

Excellent advice; this model works wonderfully. pg_catalog is normally
implicit, but you're right, system functions can be overridden by
setting the search path.


Eric Radman  |  http://eradman.com