CREATE FUNCTION fq(_quality_name text) RETURNS text AS $$ BEGIN RETURN session_user || '/' || _quality_name; END; $$ LANGUAGE PLPGSQL; CREATE FUNCTION fs(_quality_name text) RETURNS text AS $$ BEGIN RETURN substring(_quality_name from position ('/' in _quality_name)+1); END; $$ LANGUAGE PLPGSQL; select fcreateuser(session_user); NOTICE: The role is a super user. fcreateuser ------------- (1 row) -- own,qual_prov,qtt_prov,qtt_requ,qual_requ select * from finsertorder('u',fq('b'),1000,1000,fq('a')); NOTICE: owner u created CONTEXT: PL/pgSQL function fgetowner(text,boolean) line 10 at assignment PL/pgSQL function finsertorder(text,text,bigint,bigint,text) line 35 at assignment id | uuid | own | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+------+-----+----+----------+----+----------+--------+---------+------- 1 | 1-1 | 1 | 2 | 1000 | 1 | 1000 | 0 | 0 | [] (1 row) select * from finsertorder('v',fq('c'),1000,1000,fq('b')); NOTICE: owner v created CONTEXT: PL/pgSQL function fgetowner(text,boolean) line 10 at assignment PL/pgSQL function finsertorder(text,text,bigint,bigint,text) line 35 at assignment id | uuid | own | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+------+-----+----+----------+----+----------+--------+---------+------- 2 | 1-2 | 2 | 1 | 1000 | 3 | 1000 | 0 | 0 | [] (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fgetquote('w',fq('a'),1000,1000,fq('c')); NOTICE: owner w created CONTEXT: PL/pgSQL function fgetowner(text,boolean) line 10 at assignment PL/pgSQL function fgetquote(text,text,bigint,bigint,text) line 36 at assignment id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+---------------------------------------------------------------------------------------------------- 1 | 3 | 1000 | 2 | 1000 | 1000 | 1000 | {"[(1, 1, 2, 1000, 1, 1000, 1000),(2, 2, 1, 1000, 3, 1000, 1000),(0, 3, 3, 1000, 2, 1000, 1000)]"} (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fexecquote('w',1); id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3 | 3 | 1000 | 2 | 1000 | 1000 | 1000 | [[{"id":1, "own":1, "nr":2, "qtt_requ":1000, "np":1, "qtt_prov":1000, "qtt":1000, "flowr":1000 },{"id":2, "own":2, "nr":1, "qtt_requ":1000, "np":3, "qtt_prov":1000, "qtt":1000, "flowr":1000 },{"id":3, "own":3, "nr":3, "qtt_requ":1000, "np":2, "qtt_prov":1000, "qtt":1000, "flowr":1000 }]+ | | | | | | | ] (1 row) select id,uuid,nb,oruuid,grp,provider,fs(quality),qtt,receiver from vmvt order by uuid; id | uuid | nb | oruuid | grp | provider | fs | qtt | receiver ----+------+----+--------+-----+----------+----+------+---------- 1 | 1-1 | 3 | 1-3 | 1-1 | w | a | 1000 | u 2 | 1-2 | 3 | 1-1 | 1-1 | u | b | 1000 | v 3 | 1-3 | 3 | 1-2 | 1-1 | v | c | 1000 | w (3 rows) select * from fremovemvt('1-1'); fremovemvt ------------ 1 (1 row) select * from fremovemvt('1-2'); fremovemvt ------------ 1 (1 row) select * from fremovemvt('1-3'); fremovemvt ------------ 1 (1 row) select * from finsertorder('u',fq('b'),2000,1000,fq('a')); id | uuid | own | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+------+-----+----+----------+----+----------+--------+---------+------- 4 | 1-4 | 1 | 2 | 1000 | 1 | 2000 | 0 | 0 | [] (1 row) select * from finsertorder('v',fq('c'),2000,1000,fq('b')); id | uuid | own | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+------+-----+----+----------+----+----------+--------+---------+------- 5 | 1-5 | 2 | 1 | 1000 | 3 | 2000 | 0 | 0 | [] (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fgetquote('w',fq('a'),500,2000,fq('c')); id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+-------------------------------------------------------------------------------------------------- 2 | 3 | 2000 | 2 | 500 | 2000 | 500 | {"[(4, 1, 2, 1000, 1, 2000, 2000),(5, 2, 1, 1000, 3, 2000, 2000),(0, 3, 3, 2000, 2, 500, 500)]"} (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fexecquote('w',2); id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6 | 3 | 2000 | 2 | 500 | 2000 | 500 | [[{"id":4, "own":1, "nr":2, "qtt_requ":1000, "np":1, "qtt_prov":2000, "qtt":2000, "flowr":1000 },{"id":5, "own":2, "nr":1, "qtt_requ":1000, "np":3, "qtt_prov":2000, "qtt":2000, "flowr":2000 },{"id":6, "own":3, "nr":3, "qtt_requ":2000, "np":2, "qtt_prov":500, "qtt":500, "flowr":500 }]+ | | | | | | | ] (1 row) select id,uuid,nb,oruuid,grp,provider,fs(quality),qtt,receiver from vmvt order by uuid; id | uuid | nb | oruuid | grp | provider | fs | qtt | receiver ----+------+----+--------+-----+----------+----+------+---------- 4 | 1-4 | 3 | 1-6 | 1-4 | w | a | 500 | u 5 | 1-5 | 3 | 1-4 | 1-4 | u | b | 1000 | v 6 | 1-6 | 3 | 1-5 | 1-4 | v | c | 2000 | w (3 rows) select * from fremovemvt('1-4'); fremovemvt ------------ 1 (1 row) select * from fremovemvt('1-5'); fremovemvt ------------ 1 (1 row) select * from fremovemvt('1-6'); fremovemvt ------------ 1 (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fgetquote('w',fq('a'),500,1000,fq('b')); id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+------------------------------------------------------------------- 3 | 1 | 1000 | 2 | 500 | 1000 | 500 | {"[(4, 1, 2, 1000, 1, 2000, 1000),(0, 3, 1, 1000, 2, 500, 500)]"} (1 row) select id,nr,qtt_requ,np,qtt_prov,qtt_in,qtt_out,flows from fexecquote('w',3); id | nr | qtt_requ | np | qtt_prov | qtt_in | qtt_out | flows ----+----+----------+----+----------+--------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 7 | 1 | 1000 | 2 | 500 | 1000 | 500 | [[{"id":4, "own":1, "nr":2, "qtt_requ":1000, "np":1, "qtt_prov":2000, "qtt":1000, "flowr":1000 },{"id":7, "own":3, "nr":1, "qtt_requ":1000, "np":2, "qtt_prov":500, "qtt":500, "flowr":500 }]+ | | | | | | | ] (1 row) select id,uuid,nb,oruuid,grp,provider,fs(quality),qtt,receiver from vmvt order by uuid; id | uuid | nb | oruuid | grp | provider | fs | qtt | receiver ----+------+----+--------+-----+----------+----+------+---------- 7 | 1-7 | 2 | 1-7 | 1-7 | w | a | 500 | u 8 | 1-8 | 2 | 1-4 | 1-7 | u | b | 1000 | w (2 rows) select * from fremovemvt('1-7'); fremovemvt ------------ 1 (1 row) select * from fremovemvt('1-8'); fremovemvt ------------ 1 (1 row) select * from fgetstats(true); _name | cnt -----------------------------+----- number of qualities | 3 number of owners | 3 number of quotes | 0 number of orders | 7 number of movements | 8 number of quotes removed | 3 number of orders removed | 7 number of movements removed | 8 number of agreements | 3 number of orders rejected | 0 agreements with 2 partners | 1 agreements with 3 partners | 2 (12 rows) select * from fgeterrs(); _name | cnt ------------------------------+----- errors on quantities in mvts | 0 errors on agreements in mvts | 0 (2 rows)