[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables?
On 11 October 2011 16:06, Eric Radman ericsh...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables?
On Tue, Oct 11, 2011 at 04:26:47PM +0200, Alban Hertroys wrote: On 11 October 2011 16:06, Eric Radman ericsh...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Global Variables in plpgsql
Hi, Can anyone know how to define global variable in plpgsql? Thanks Regards, Raj
Re: [GENERAL] Global Variables in plpgsql
On 04/11/2011 03:53 AM, Nick Raj wrote: Hi, Can anyone know how to define global variable in plpgsql? Thanks Global to what? Regards, Raj -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables in plpgsql
Hey Nick, 2011/4/11 Nick Raj nickrajj...@gmail.com Hi, Can anyone know how to define global variable in plpgsql? Thanks Why if you are already inside a database system ? :-) Just use tables. Regards, Raj -- // Dmitriy.
