src/pgperf/_pgperf.pl

#!/usr/bin/perl

use strict;

my $table = shift;

sub read_sql_file {
	my $file = $_[0];
	
	open(F, "$file") || die($!);

	my $sql;
	while(<F>)
	{
		$sql .= $_;
	}

	close(F);

	$sql;
}

sub def_create_snapshot_table {
	my $tab = $_[0];
	my $sql = $_[1];
	
	$sql =~ s/;//;
	my $s =<<EOF;
--
-- CREATE TABLE pgperf.snapshot_${tab}
--
DROP TABLE IF EXISTS pgperf.snapshot_${tab};

CREATE TABLE pgperf.snapshot_${tab} AS
$sql
LIMIT 0;

ALTER TABLE pgperf.snapshot_${tab} ALTER COLUMN sid SET NOT NULL;
ALTER TABLE pgperf.snapshot_${tab} OWNER TO pgperf;

CREATE INDEX snapshot_${tab}_sid_idx ON pgperf.snapshot_${tab}(sid);

-- \\d pgperf.snapshot_${tab}

EOF

	$s;
}


sub def_create_snapshot_func {
	my $tab = $_[0];
	my $sql = $_[1];
	
	$sql =~ s/0::int as sid,/_sid,/i;
	$sql =~ s/;//i;

	my $s =<<EOF;
--
-- CREATE FUNCTION pgperf.create_snapshot_${tab} (INT)
--
CREATE OR REPLACE FUNCTION pgperf.create_snapshot_${tab} (INT)
  RETURNS boolean AS
\$\$
DECLARE
	_sid ALIAS FOR \$1;
BEGIN
  INSERT INTO pgperf.snapshot_${tab} (
	$sql
  );
  
  RETURN true;
END
\$\$
LANGUAGE 'plpgsql';

ALTER FUNCTION pgperf.create_snapshot_${tab}(INT) OWNER TO pgperf;

EOF

	$s;
}

sub def_delete_snapshot_func {
	my $tab = $_[0];
	my $sql = $_[1];
	
	my $s =<<EOF;
--
-- CREATE FUNCTION pgperf.delete_snapshot_${tab} (INT)
--
CREATE OR REPLACE FUNCTION pgperf.delete_snapshot_${tab} (INT)
  RETURNS boolean AS
\$\$
DECLARE
	_sid ALIAS FOR \$1;
BEGIN
  DELETE FROM pgperf.snapshot_${tab} WHERE sid = _sid;
  RETURN true;
END
\$\$
LANGUAGE 'plpgsql';

ALTER FUNCTION pgperf.delete_snapshot_${tab}(INT) OWNER TO pgperf;

EOF

	$s;
}

sub def_test_snapshot {
	my $tab = $_[0];
	my $sql = $_[1];

	my $s =<<EOF;
--
-- TEST pgperf.snapshot_${tab}
--
/*
SELECT pgperf.create_snapshot_${tab}(1);
SELECT count(*) FROM pgperf.snapshot_${tab};
SELECT * FROM pgperf.snapshot_${tab};
SELECT pgperf.delete_snapshot_${tab}(1);
SELECT count(*) FROM pgperf.snapshot_${tab};
SELECT * FROM pgperf.snapshot_${tab};
SELECT pgperf.create_snapshot_${tab}(1);
*/

EOF

	$s;
}

my $sql = &read_sql_file($table . ".sql");

my $sql2 = def_create_snapshot_table($table, $sql);
my $sql3 = def_create_snapshot_func($table, $sql);
my $sql4 = def_delete_snapshot_func($table, $sql);
#my $sql5 = def_test_snapshot($table, $sql);

#print $sql;

print $sql2;
print $sql3;
print $sql4;
#print $sql5;