I wanted to release at least the direct PHP function (I'll eventually release the tools I'm building to something like Github) though I don't yet have the time to do much else other than post it here at least. Hopefully this will save others time and please feel free to suggest improvements. I'm not sure how secure the code is (it's merely a tool I'm only using for development and currently for a client who I'm setting up an intranet-only website) though I did my best to heed the advice from the other questions/answers by Erwin Brandstetter and Joishi Bodio.
Sample URL request:
db_table_duplicate.php?db=database_name&table1=parts2&table2=parts3
I simply have PHP call functions and since I often repeat the actions my PostgreSQL tools execute I simply store the information in a $_GET
array.
function dbtd_04_commit()
{
echo '<p><strong>Step 4/4:</strong> Duplicating the table `'.$_GET['table1'].'`...</p>'."\n";
$table2 = pg_escape_string($_SESSION['db'],$_GET['table2']);
$query1 = "SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name='$table2';";
$result1 = pg_query($_SESSION['db'],$query1);
if ($result1)
{
$count1 = pg_num_rows($result1);
$table1 = pg_escape_string($_SESSION['db'],$_GET['table1']);
if ($count1>0)
{
echo '<div><p><strong class="error">Error:</strong> the table '.$table2.' already exists; the table '.$table1.' was <em>not</em> duplicated. Would you like to <a href="tools/'.$GLOBALS['paths']->file.'?db='.$_GET['db'].'&table1='.$_GET['table1'].'">try a different name</a>?</p></div>'."\n";
}
else
{//Check for custom table-structure duplication function.
$query2 = "SELECT p.proname FROM pg_catalog.pg_namespace AS n JOIN pg_catalog.pg_proc AS p ON p.pronamespace=n.oid WHERE n.nspname='public' AND proname='f_copy_tbl';";
$result2 = pg_query($_SESSION['db'],$query2);
if ($result2)
{
$count2 = pg_num_rows($result2);
if ($count2==0)
{
$query3 = "CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text) RETURNS void AS $func$"."\n";
$query3 .= "DECLARE _sql text;"."\n";
$query3 .= "BEGIN"."\n";
//-- Copy table
$query3 .= "EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);"."\n";
//-- Fix serial columns, if any
$query3 .= "SELECT INTO _sql"."\n";
$query3 .= " string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'"."\n";
$query3 .= " || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'"."\n";
$query3 .= " , seq, _newtbl, a.attname), E';\n') || E';\n'"."\n";
$query3 .= " || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n '"."\n";
$query3 .= " || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$"."\n";
$query3 .= " , a.attname, seq), E'\n, ')"."\n";
$query3 .= "FROM pg_attribute a"."\n";
$query3 .= "INNER JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum, quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq"."\n";// -- new seq name
$query3 .= "WHERE a.attrelid = _tbl"."\n";
$query3 .= "AND a.attnum > 0"."\n";
$query3 .= "AND NOT a.attisdropped"."\n";
$query3 .= "AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])"."\n";
$query3 .= "AND ad.adsrc = 'nextval('''"."\n";
$query3 .= " || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass"."\n";
$query3 .= " || '''::regclass)';"."\n";
$query3 .= "IF _sql IS NOT NULL THEN"."\n";
$query3 .= " EXECUTE _sql;"."\n";
$query3 .= "END IF;"."\n";
$query3 .= "END"."\n";
$query3 .= "$func$ LANGUAGE plpgsql VOLATILE;"."\n";
$result3 = pg_query($_SESSION['db'],$query3);
if ($result3) {}
else {sql_error_report($query3,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
$query4 = "SELECT f_copy_tbl('$table1', '$table2');";
$result4 = pg_query($_SESSION['db'],$query4);
if ($result4)
{
$query5 = 'DO'."\n";
$query5 .= '$do$'."\n";
$query5 .= 'BEGIN'."\n";
$query5 .= ' INSERT INTO '.$table2.' SELECT * FROM '.$table1.';'."\n";
$query5 .= ' EXECUTE ('."\n";
$query5 .= ' SELECT format($$SELECT setval('."'%s'::regclass, max(%I)) FROM %s".'$$, pg_get_serial_sequence(a.attrelid::regclass::text, a.attname), a.attname, a.attrelid::regclass'."\n";
$query5 .= ' )'."\n";
$query5 .= ' FROM pg_index i'."\n";
$query5 .= ' JOIN pg_attribute a ON a.attrelid = i.indrelid'."\n";
$query5 .= ' AND a.attnum = i.indkey[0]'."\n";
$query5 .= " WHERE i.indrelid = '".$table2."'::regclass"."\n";
$query5 .= ' AND i.indisprimary'."\n";
$query5 .= ' );'."\n";
$query5 .= 'END'."\n";
$query5 .= '$do$ LANGUAGE plpgsql;';
$result5 = pg_query($_SESSION['db'],$query5);
if ($result5)
{
$query6 = "VACUUM $table2;";
$result6 = pg_query($_SESSION['db'],$query6);
if ($result6)
{
echo '<div><p><strong>SUCCESS!</strong> The table '.$table1.' has been duplicated.</p></div>'."\n";
}
else {sql_error_report($query6,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
else {sql_error_report($query5,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
else {sql_error_report($query4,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
else {sql_error_report($query2,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
}
else {sql_error_report($query1,pg_last_error($_SESSION['db']),__FUNCTION__);}
}