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

set console title and prompt in bash

To set the title of your console window in a bash terminal put the following in .bashrc:

PROMPT_COMMAND='echo -ne "\033]0;${USER}@${HOSTNAME}\007"

This will set the console title to user@host.

It can also be useful to have the host and pwd in the command prompt, that can be achieved with:

PS1='${HOSTNAME}: $PWD -> '

The achieve the same in a cshell window put the following in your .cshrc :

alias setprompt 'set prompt = "`uname -n`: `pwd` -> "'
alias cd 'cd \!*;setprompt;'

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);
}