blocks|key|1309635|text|为什么不使用视图呢？也许views会对你的情况有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1309636|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.2/static/sql-createview.html^0|C|5|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Why not use a view? May be <a href="http://www.postgresql.org/docs/9.2/static/sql-createview.html" rel="nofollow">views</a> will help in your case.

blocks|key|3091170|text|我不知道有什么可能。只有基于psql变量的psql才有解决方法，但是有很多限制-使用参数进行这种查询是困难的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3091171|postgres=#+\set+whoami+'SELECT+CURRENT_USER;'
postgres=#+:whoami
+current_user+
--------------
pavel
(1+row)|code-block|syntax|javascript|3091172|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I don't know about any possibility. There is only workaround for psql based on psql variables, but there is lot of limits - using parameters for this queries is difficult.

<pre><code>postgres=# \set whoami 'SELECT CURRENT_USER;'
postgres=# :whoami
 current_user 
--------------
pavel
(1 row)
</code></pre>

blocks|key|1309648|text|Pavel的答案几乎是正确的，除非你可以以另一种方式使用参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1309649|之后|1309650|\set+s+'select+*+from+'
\set+l+'+limit+10;'|code-block|syntax|javascript|1309651|以下命令|1309652|:s+agent+:l|1309653|将等于|1309654|select+*+from+agent+limit+10;|1309655|根据http://www.postgresql.org/docs/9.0/static/app-psql.html的说法|offset|length|1309656|1309657|如果未加引号的参数以冒号(:)开头，则将其视为psql变量，并将变量的值用作参数。如果变量名括在单引号中(例如:'var')，它将作为SQL文本进行转义，并将结果用作参数。如果变量名括在双引号中，则将其转义为SQL标识符，并将结果用作参数。|blockquote|1309658|1309659|1309660|您还可以使用反引号来运行shell命令|1309661|用反引号(`)括起来的|1309662|参数被当作传递给shell的命令行。该命令的输出(删除了所有尾随的换行符)将作为参数值。上面的转义序列也适用于反引号。|1309663|1309664|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.0/static/app-psql.html^0|0|0|0|0|0|0|0|2|1J|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1G|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1H|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1I|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1J|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1K|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1L|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1M|8|@]|9|@$S|1N|T|1O|1|1P]]|A|$]]|$1|U|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|V|3|W|5|X|7|1R|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1U|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1V|8|@]|9|@]|A|$]]|$1|14|3|15|5|X|7|1W|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|18|$19|$5|1A|1B|1C|A|$1D|1E]]]]

Pavel's answer is almost correct, except you can use parameter in another way.

after 

<pre><code>\set s 'select * from '
\set l ' limit 10;'
</code></pre>

The following command 

<pre><code>:s agent :l
</code></pre>

will equal to 

<pre><code>select * from agent limit 10;
</code></pre>

According to <a href="http://www.postgresql.org/docs/9.0/static/app-psql.html" rel="noreferrer">http://www.postgresql.org/docs/9.0/static/app-psql.html</a>

<blockquote>
 If an unquoted argument begins with a colon (:), it is taken as a psql
 variable and the value of the variable is used as the argument
 instead. If the variable name is surrounded by single quotes (e.g.
 :'var'), it will be escaped as an SQL literal and the result will be
 used as the 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 the argument.
</blockquote>

You can also use backquote to run shell command

<blockquote>
 Arguments that are enclosed in backquotes (`) are taken as a command
 line that is passed to the shell. The output of the command (with any
 trailing newline removed) is taken as the argument value. The above
 escape sequences also apply in backquotes.
</blockquote>

blocks|key|1309631|text|使用UDF怎么样？您可以创建一个返回表(集合)的UDF，然后可以按如下方式查询它:+select+*+from+udf()；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1309632|它不是很干净，但总比什么都没有好，而且它是可移植的。而且UDF也可以接受参数。|1309633|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

how about using UDFs? You can create a UDF that returns a table (set of) then you can query it as this: select * from udf();

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

blocks|key|1309677|text|如果您需要从命令行(而不是psql+cli)运行频繁的查询，这可能会有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1309678|将其添加到.bash_profile+/.bashrc|offset|length|style|CODE|1309679|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}"+%7C%7C
++++$psqlcommand+"SELECT+*+from+$1"
}|code-block|syntax|javascript|1309680|上面的命令从表中选择行，并传递到参数中。|1309681|备注：|BOLD|1309682|1309683|根据需要更改数据库名称。|ordered-list-item|1309684|默认情况下方案为public。要使用另一个默认模式，请在~/.psqlrc文件中添加以下行。|1309685|1309686|SET+SEARCH_PATH+TO+<schema_name>;|1309687|1309688|如果数据库受密码保护，则返回refer+this并使用安全方法。|1309689|1309690|我做了一些命令供我使用，如果它可能有帮助的话。|1309691|1309692|psqlselectrows+-从table|1309693|psqlgettablecount中选择行-获取table|1309694|psqltruncatetable的行数-截断表，在prompt|1309695|psqlgettablesize上-获取table|1309696|psqlgetvacuumdetails的大小-获取table|1309697|psqlsettings的真空细节-获取为Postgres配置的默认和修改的设置。|1309698|1309699|(以上所有命令都需要表名作为第一个参数)|1309700|#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}"+%7C%7C
++++$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}"+%7C%7C
++++$psqlcommand+"select+count(*)+from+$1;"
}
function+psqlgetvacuumdetails()
{
++++[+-z+"$1"+]+&&+echo+-e+"${B_RED}Argument+1+missing:+Need+table+name${RESET}"+%7C%7C
++++$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}"+%7C%7C
++++{
++++++++read+-p+"$(echo+-e+${B_YELLOW}"Are+you+sure+to+truncate+table+'$1'+(y/n)?+"${RESET})"+choice
++++++++case+"$choice"+in
++++++++++++y%7CY+)+$psqlcommand+"TRUNCATE+$1;";;
++++++++++++n%7CN+)+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+'%25$1%25'"
++++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}"+%7C%7C
++++$psqlcommand+"SELECT+*+from+$1"
}|1309701|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/questions/14740/how-to-use-psql-with-no-password-prompt^0|0|5|D|K|7|0|0|0|0|3|0|0|0|S|9|0|0|0|X|0|0|E|A|0|0|0|0|0|0|E|0|0|H|0|0|H|0|0|G|0|0|K|0|0|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1X|8|@$D|1Y|E|1Z|F|G]|$D|20|E|21|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|22|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|23|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|24|8|@$D|25|E|26|F|Q]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|27|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|28|8|@]|9|@]|A|$]]|$1|V|3|W|5|U|7|29|8|@$D|2A|E|2B|F|G]]|9|@]|A|$]]|$1|X|3|-4|5|6|7|2C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2D|8|@$D|2E|E|2F|F|G]]|9|@]|A|$]]|$1|10|3|-4|5|6|7|2G|8|@]|9|@]|A|$]]|$1|11|3|12|5|U|7|2H|8|@]|9|@$D|2I|E|2J|1|2K]]|A|$]]|$1|13|3|-4|5|6|7|2L|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|2M|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|2N|8|@]|9|@]|A|$]]|$1|17|3|18|5|U|7|2O|8|@$D|2P|E|2Q|F|Q]]|9|@]|A|$]]|$1|19|3|1A|5|U|7|2R|8|@$D|2S|E|2T|F|Q]]|9|@]|A|$]]|$1|1B|3|1C|5|U|7|2U|8|@$D|2V|E|2W|F|Q]]|9|@]|A|$]]|$1|1D|3|1E|5|U|7|2X|8|@$D|2Y|E|2Z|F|Q]]|9|@]|A|$]]|$1|1F|3|1G|5|U|7|30|8|@$D|31|E|32|F|Q]]|9|@]|A|$]]|$1|1H|3|1I|5|U|7|33|8|@$D|34|E|35|F|Q]]|9|@]|A|$]]|$1|1J|3|-4|5|6|7|36|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|37|8|@]|9|@]|A|$]]|$1|1M|3|1N|5|J|7|38|8|@]|9|@]|A|$K|L]]|$1|1O|3|-4|5|6|7|39|8|@]|9|@]|A|$]]]|1P|$1Q|$5|1R|1S|1T|A|$1U|1V]]]]

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

Add this to <code>.bash_profile</code> /<code>.bashrc</code>

<pre><code>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" ] &amp;&amp; echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" ||
 $psqlcommand "SELECT * from $1"
}
</code></pre>

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

Note:

<ol>
<li>Change the database name, as required.</li>
<li>The schema by default is public. To have another default schema, add the following line in <code>~/.psqlrc</code> file.</li>
</ol>

<code>SET SEARCH_PATH TO &lt;schema_name&gt;;</code>

<ol start="3">
<li>If the database is password protected, <a href="https://dba.stackexchange.com/questions/14740/how-to-use-psql-with-no-password-prompt">refer this</a> and make use of the secure method.</li>
</ol>

I have made some commands for my use, if it might help.

<ol>
<li>psqlselectrows - To select rows from a table</li>
<li>psqlgettablecount - To get row count of a table</li>
<li>psqltruncatetable - To truncate a table, on prompt</li>
<li>psqlgettablesize - To get the size of a table</li>
<li>psqlgetvacuumdetails - To get vacuum details of a table</li>
<li>psqlsettings - To get default and modified settings configured for Postgres.</li>
</ol>

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

<pre><code>#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" ] &amp;&amp; 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" ] &amp;&amp; echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" ||
 $psqlcommand "select count(*) from $1;"
}
function psqlgetvacuumdetails()
{
 [ -z "$1" ] &amp;&amp; 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" ] &amp;&amp; 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" ] &amp;&amp; echo -e "${B_RED}Argument 1 missing: Need table name${RESET}" ||
 $psqlcommand "SELECT * from $1"
}
</code></pre>

Is it possible to somehow create aliases (like Unix <code>alias</code> command) in psql?

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

psql shortcut for frequently used queries? (like Unix "alias")

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

有没有可能以某种方式在psql中创建别名(比如Unix alias命令)？我的意思是，不是SQL函数，而是本地别名，以简化手动查询？

问常用查询的psql快捷方式？(如Unix的“别名”)
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问常用查询的psql快捷方式？(如Unix的“别名”)EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问常用查询的psql快捷方式？(如Unix的“别名”)
EN