Psql for commonly used queries? (e.g. Unix alias)

Question

Is it possible to somehow create aliases (e.g. Unix alias ) in psql?

I do not mean SQL FUNCTION, but local aliases to facilitate manual queries?

Answer 1

Why not use a view? Maybe views will help in your case.

Answer 2

I do not know about any possibility. There is only a workaround for psql based on psql variables, but there are many limitations - using parameters for these queries is difficult.

 postgres=# \set whoami 'SELECT CURRENT_USER;' postgres=# :whoami current_user -------------- pavel (1 row) 

Answer 3

Paul's answer is almost correct, except that you can use the parameter differently.

after

 \set s 'select * from ' \set l ' limit 10;' 

Next command

 :s agent :l 

will be equal

 select * from agent limit 10; 

According to http://www.postgresql.org/docs/9.0/static/app-psql.html

If an unquoted argument begins with a colon (:), it is taken as a psql variable, and the value of the variable is used as an argument instead. If the variable name is surrounded by single quotes (for example:: 'var'), it will be escaped as an SQL literal, and the result will be used as an argument. If the variable name is surrounded by double quotes, it will be escaped as an SQL identifier, and the result will be used as an argument.

You can also use backquote to run a shell command

Arguments enclosed in backquotes (`) are taken as the command that is passed to the shell. The output of the command (with any as the value of the argument, a final new line is taken) The above escape sequences also apply to backquotes.

Answer 4

How about using UDF? You can create a UDF that returns a table (set), after which you can query it as follows: select * from udf ();

It is not so clean, but it is better than nothing, and it is portable. And UDFs can also take parameters.

Answer 5

This can help if you need to run frequent queries from the command line (not from psql cli).

Add this to .bash_profile / .bashrc

 POSTGRES_BIN=~/Postgres/bin B_RED='\033[1;31m' RESET='\033[0m' psqlcommand="$POSTGRES_BIN/psql -U vignesh usersdb -q -c" function psqlselectrows() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || $psqlcommand "SELECT * from $1" } 

The above command selects the rows from the table passed in the argument.

Remarks:

1. Change the database name as required.
2. The default schema is public. To have a different default scheme, add the following line to the ~/.psqlrc .

SET SEARCH_PATH TO <schema_name>;

3. If the database is password protected, refer to this and use the secure method.

I have made several commands for my use if this can help.

1. psqlselectrows - to select rows from a table
2. psqlgettablecount - get the number of rows in a table
3. psqltruncatetable - crop table on request
4. psqlgettablesize - get table size
5. psqlgetvacuumdetails - get vacuum table details
6. psqlsettings - to get the default settings and changed settings configured for Postgres.

(All of the above commands need a table name as the first argument)

 #Colors B_RED='\033[1;31m' B_GREEN='\033[1;32m' B_YELLOW='\033[1;33m' RESET='\033[0m' #Postgres Command With Params psqlcommand="$POSTGRES_BIN/psql -U vignesh usersdb -q -c" function psqlgettablesize() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || $psqlcommand "select pg_size_pretty(pg_total_relation_size('$1')) as total_table_size, pg_size_pretty(pg_relation_size('$1')) as table_size, pg_size_pretty(pg_indexes_size('$1')) as index_size;"; } function psqlgettablecount() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || $psqlcommand "select count(*) from $1;" } function psqlgetvacuumdetails() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || $psqlcommand "SELECT relname, n_live_tup, n_dead_tup, last_analyze::timestamp, analyze_count, last_autoanalyze::timestamp, autoanalyze_count, last_vacuum::timestamp, vacuum_count, last_autovacuum::timestamp, autovacuum_count FROM pg_stat_user_tables where relname='$1' and schemaname = current_schema();" } function psqltruncatetable() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || { read -p "$(echo -e ${B_YELLOW}"Are you sure to truncate table '$1' (y/n)? "${RESET})" choice case "$choice" in y|Y ) $psqlcommand "TRUNCATE $1;";; n|N ) echo -e "${B_GREEN}Table '$1' not truncated${RESET}";; * ) echo -e "${B_RED}Invalid option${RESET}";; esac } } function psqlsettings() { query="select * from pg_settings" if [ "$1" != "" ]; then query="$query where category like '%$1%'" fi query="$query ;" $psqlcommand "$query" if [ -z "$1" ]; then echo -e "${B_YELLOW}Passing Category as first argument will filter the related settings.${RESET}" fi } function psqlselectrows() { [ -z "$1" ] && echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" || $psqlcommand "SELECT * from $1" }