DROP TABLE IF EXISTS  table_parent ;
CREATE TABLE table_parent
(
id SERIAL ,
name text ,
curr_value numeric
);

DROP TABLE IF EXISTS  table_child1  ;
CREATE TABLE table_child1
(
id SERIAL ,
parent_key integer ,
name text ,
curr_value numeric
);

DROP TABLE IF EXISTS table_child2 ;
CREATE TABLE table_child2
(
id integer ,
parent_key integer ,
name text ,
curr_value numeric
);


INSERT INTO table_parent ( name , curr_value )
SELECT i::text , i::numeric
FROM generate_series(1, 1000000) AS t(i) ;

INSERT INTO table_child1 ( parent_key , name , curr_value )
SELECT i::numeric , i::text , i::numeric
FROM generate_series(300, 900000) AS t(i) ;

INSERT INTO table_child2 ( id , parent_key , name , curr_value )
SELECT i::numeric , i::numeric , i::text , i::numeric
FROM generate_series(20000, 800000) AS t(i) ;

CREATE INDEX table_parent_idx ON table_parent ( id );
CREATE INDEX table_child1_idx ON table_child1 ( parent_key );
CREATE INDEX table_child2_idx ON table_child2 ( parent_key );

ANALYZE table_parent ;
ANALYZE table_child1 ;
ANALYZE table_child2 ;

CREATE OR REPLACE FUNCTION generator3() RETURNS integer AS $$
DECLARE
result_rec record ;
BEGIN
SELECT child1.name , child2.name
INTO      result_rec
FROM   table_parent parent
      join table_child1 child1 ON ( parent.id = child1.parent_key )
      join table_child2 child2 ON ( parent.id = child2.parent_key )                     
WHERE
      ( parent.id > 500000 AND parent.id < 500100 )   and ( parent.id % 2 = 0 );

INSERT INTO table_child2 ( id , parent_key , name , curr_value )
SELECT i::numeric , i::numeric , i::text , i::numeric
FROM generate_series(900000, 910000) AS t(i) ;

UPDATE table_child2
SET name = name || 'test'
WHERE id between 900000 AND 910000 ;
               
DELETE FROM table_child2
WHERE id between 900000 AND 910000 ;

return 0 ;
END
$$ LANGUAGE plpgsql;

#!/bin/sh
#generator3.4.sh
#version.3.4

script=$(readlink -f $0)
current_path=`dirname $script`


LOG_FILE=$current_path'/generator.log'

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : START '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : START '> $LOG_FILE

/opt/pgpro/ent-15/bin/psql -d test_db -c "select pg_stat_reset()" >> $LOG_FILE
if [ $? -ne 0 ]
then
  ##################################################################### 
  #ОШИБКА СУБД  
  echo 'ERROR : PostgreSQL ERROR : pg_stat_reset TERMINATED WITH ERROR '
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR ' >> $LOG_FILE
  exit 1000
  #####################################################################
fi


/opt/pgpro/ent-15/bin/psql -d test_db -c "select pg_stat_statements_reset()" >> $LOG_FILE
if [ $? -ne 0 ]
then
  ##################################################################### 
  #ОШИБКА СУБД  
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR '
  echo 'ERROR : PostgreSQL ERROR : pg_stat_statements_reset TERMINATED WITH ERROR ' >> $LOG_FILE
  exit 1000
  #####################################################################
fi


for ((i=1; i <= 1000; i++))
do
	echo 'PASS = '$i
	echo 'PASS = '$i>> $LOG_FILE
	
	/opt/pgpro/ent-15/bin/psql -d test_db -c "select generator3()" 
	if [ $? -ne 0 ]
	then
		##################################################################### 
		#ОШИБКА СУБД  
		echo 'ERROR : PostgreSQL ERROR : generator TERMINATED WITH ERROR '
		echo 'ERROR : PostgreSQL ERROR : generator TERMINATED WITH ERROR ' >> $LOG_FILE
		exit 1000
		#####################################################################
	fi
	

done

echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '
echo 'TIMESTAMP : '$(date "+%d-%m-%Y %H:%M:%S") ' : OK : FINISHED '>> $LOG_FILE

exit 0