Select rows with duplicate fields - postgres sql

The following query returns the field and count of rows in a table with duplicate data in the given field.

SELECT , 
 COUNT() AS NumOccurrences
FROM 
GROUP BY 
HAVING ( COUNT() > 1 )

postgres - run query on all tables

The following is an example in bash of applying a query to every table in the public schema on database template1:

for table in `psql -qAt -c "select tablename from pg_tables where schemaname in ('public');" template1` ; do  psql -c "alter table public.$table drop column if exists lastedit" template1 ; done

postgres - alter owner of all functions in schema

Firstly this query will return the function name including argument definition and the schema it belongs to. The function name alone is not enough to perform an ALTER FUNCTION query due to the prossibilities of overloaded functions.

select proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')', n.nspname from pg_proc pr join pg_namespace n on pr.pronamespace = n.oid where n.nspname in ('public','myschema');

SQL to change the owner:

ALTER FUNCTION %s OWNER TO new_owner;


Below is an example of the above using a simple perl script:


$query = $dbh->prepare (qq{select proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')', n.nspname from pg_proc pr join pg_namespace n on pr.pronamespace = n.oid where n.nspname in ($namespaces);});
$query->execute ();

while (($Table, $Schema) = $query->fetchrow ()) {
$Command = sprintf "ALTER FUNCTION %s.%s OWNER TO test; \n", $Schema, $Table;
        $i = $dbh->do ($Command);
}