Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit49ca462

Browse files
committed
Add \gdesc psql command.
This command acts somewhat like \g, but instead of executing the querybuffer, it merely prints a description of the columns that the queryresult would have. (Of course, this still requires parsing the query;if parse analysis fails, you get an error anyway.) We accomplish thisusing an unnamed prepared statement, which should be invisible to psqlusers.Pavel Stehule, reviewed by Fabien CoelhoDiscussion:https://postgr.es/m/CAFj8pRBhYVvO34FU=EKb=nAF5t3b++krKt1FneCmR0kuF5m-QA@mail.gmail.com
1 parent6e427aa commit49ca462

File tree

8 files changed

+293
-6
lines changed

8 files changed

+293
-6
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1949,6 +1949,25 @@ Tue Oct 26 21:40:57 CEST 1999
19491949
</varlistentry>
19501950

19511951

1952+
<varlistentry>
1953+
<term><literal>\gdesc</literal></term>
1954+
1955+
<listitem>
1956+
<para>
1957+
Shows the description (that is, the column names and data types)
1958+
of the result of the current query buffer. The query is not
1959+
actually executed; however, if it contains some type of syntax
1960+
error, that error will be reported in the normal way.
1961+
</para>
1962+
1963+
<para>
1964+
If the current query buffer is empty, the most recently sent query
1965+
is described instead.
1966+
</para>
1967+
</listitem>
1968+
</varlistentry>
1969+
1970+
19521971
<varlistentry>
19531972
<term><literal>\gexec</literal></term>
19541973

‎src/bin/psql/command.c

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,7 @@ static backslashResult exec_command_errverbose(PsqlScanState scan_state, bool ac
8888
staticbackslashResultexec_command_f(PsqlScanStatescan_state,boolactive_branch);
8989
staticbackslashResultexec_command_g(PsqlScanStatescan_state,boolactive_branch,
9090
constchar*cmd);
91+
staticbackslashResultexec_command_gdesc(PsqlScanStatescan_state,boolactive_branch);
9192
staticbackslashResultexec_command_gexec(PsqlScanStatescan_state,boolactive_branch);
9293
staticbackslashResultexec_command_gset(PsqlScanStatescan_state,boolactive_branch);
9394
staticbackslashResultexec_command_help(PsqlScanStatescan_state,boolactive_branch);
@@ -337,6 +338,8 @@ exec_command(const char *cmd,
337338
status=exec_command_f(scan_state,active_branch);
338339
elseif (strcmp(cmd,"g")==0||strcmp(cmd,"gx")==0)
339340
status=exec_command_g(scan_state,active_branch,cmd);
341+
elseif (strcmp(cmd,"gdesc")==0)
342+
status=exec_command_gdesc(scan_state,active_branch);
340343
elseif (strcmp(cmd,"gexec")==0)
341344
status=exec_command_gexec(scan_state,active_branch);
342345
elseif (strcmp(cmd,"gset")==0)
@@ -1330,6 +1333,23 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
13301333
returnstatus;
13311334
}
13321335

1336+
/*
1337+
* \gdesc -- describe query result
1338+
*/
1339+
staticbackslashResult
1340+
exec_command_gdesc(PsqlScanStatescan_state,boolactive_branch)
1341+
{
1342+
backslashResultstatus=PSQL_CMD_SKIP_LINE;
1343+
1344+
if (active_branch)
1345+
{
1346+
pset.gdesc_flag= true;
1347+
status=PSQL_CMD_SEND;
1348+
}
1349+
1350+
returnstatus;
1351+
}
1352+
13331353
/*
13341354
* \gexec -- send query and execute each field of result
13351355
*/

‎src/bin/psql/common.c

Lines changed: 128 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include"fe_utils/mbprint.h"
3030

3131

32+
staticboolDescribeQuery(constchar*query,double*elapsed_msec);
3233
staticboolExecQueryUsingCursor(constchar*query,double*elapsed_msec);
3334
staticboolcommand_no_begin(constchar*query);
3435
staticboolis_select_command(constchar*query);
@@ -1323,8 +1324,15 @@ SendQuery(const char *query)
13231324
}
13241325
}
13251326

1326-
if (pset.fetch_count <=0||pset.gexec_flag||
1327-
pset.crosstab_flag|| !is_select_command(query))
1327+
if (pset.gdesc_flag)
1328+
{
1329+
/* Describe query's result columns, without executing it */
1330+
OK=DescribeQuery(query,&elapsed_msec);
1331+
ResetCancelConn();
1332+
results=NULL;/* PQclear(NULL) does nothing */
1333+
}
1334+
elseif (pset.fetch_count <=0||pset.gexec_flag||
1335+
pset.crosstab_flag|| !is_select_command(query))
13281336
{
13291337
/* Default fetch-it-all-and-print mode */
13301338
instr_timebefore,
@@ -1467,6 +1475,9 @@ SendQuery(const char *query)
14671475
pset.gset_prefix=NULL;
14681476
}
14691477

1478+
/* reset \gdesc trigger */
1479+
pset.gdesc_flag= false;
1480+
14701481
/* reset \gexec trigger */
14711482
pset.gexec_flag= false;
14721483

@@ -1482,6 +1493,118 @@ SendQuery(const char *query)
14821493
}
14831494

14841495

1496+
/*
1497+
* DescribeQuery: describe the result columns of a query, without executing it
1498+
*
1499+
* Returns true if the operation executed successfully, false otherwise.
1500+
*
1501+
* If pset.timing is on, total query time (exclusive of result-printing) is
1502+
* stored into *elapsed_msec.
1503+
*/
1504+
staticbool
1505+
DescribeQuery(constchar*query,double*elapsed_msec)
1506+
{
1507+
PGresult*results;
1508+
boolOK;
1509+
instr_timebefore,
1510+
after;
1511+
1512+
*elapsed_msec=0;
1513+
1514+
if (pset.timing)
1515+
INSTR_TIME_SET_CURRENT(before);
1516+
1517+
/*
1518+
* To parse the query but not execute it, we prepare it, using the unnamed
1519+
* prepared statement. This is invisible to psql users, since there's no
1520+
* way to access the unnamed prepared statement from psql user space. The
1521+
* next Parse or Query protocol message would overwrite the statement
1522+
* anyway. (So there's no great need to clear it when done, which is a
1523+
* good thing because libpq provides no easy way to do that.)
1524+
*/
1525+
results=PQprepare(pset.db,"",query,0,NULL);
1526+
if (PQresultStatus(results)!=PGRES_COMMAND_OK)
1527+
{
1528+
psql_error("%s",PQerrorMessage(pset.db));
1529+
ClearOrSaveResult(results);
1530+
return false;
1531+
}
1532+
PQclear(results);
1533+
1534+
results=PQdescribePrepared(pset.db,"");
1535+
OK=AcceptResult(results)&&
1536+
(PQresultStatus(results)==PGRES_COMMAND_OK);
1537+
if (OK&&results)
1538+
{
1539+
if (PQnfields(results)>0)
1540+
{
1541+
PQExpBufferDatabuf;
1542+
inti;
1543+
1544+
initPQExpBuffer(&buf);
1545+
1546+
printfPQExpBuffer(&buf,
1547+
"SELECT name AS \"%s\", pg_catalog.format_type(tp, tpm) AS \"%s\"\n"
1548+
"FROM (VALUES ",
1549+
gettext_noop("Column"),
1550+
gettext_noop("Type"));
1551+
1552+
for (i=0;i<PQnfields(results);i++)
1553+
{
1554+
constchar*name;
1555+
char*escname;
1556+
1557+
if (i>0)
1558+
appendPQExpBufferStr(&buf,",");
1559+
1560+
name=PQfname(results,i);
1561+
escname=PQescapeLiteral(pset.db,name,strlen(name));
1562+
1563+
if (escname==NULL)
1564+
{
1565+
psql_error("%s",PQerrorMessage(pset.db));
1566+
PQclear(results);
1567+
termPQExpBuffer(&buf);
1568+
return false;
1569+
}
1570+
1571+
appendPQExpBuffer(&buf,"(%s, '%u'::pg_catalog.oid, %d)",
1572+
escname,
1573+
PQftype(results,i),
1574+
PQfmod(results,i));
1575+
1576+
PQfreemem(escname);
1577+
}
1578+
1579+
appendPQExpBufferStr(&buf,") s(name, tp, tpm)");
1580+
PQclear(results);
1581+
1582+
results=PQexec(pset.db,buf.data);
1583+
OK=AcceptResult(results);
1584+
1585+
if (pset.timing)
1586+
{
1587+
INSTR_TIME_SET_CURRENT(after);
1588+
INSTR_TIME_SUBTRACT(after,before);
1589+
*elapsed_msec+=INSTR_TIME_GET_MILLISEC(after);
1590+
}
1591+
1592+
if (OK&&results)
1593+
OK=PrintQueryResults(results);
1594+
1595+
termPQExpBuffer(&buf);
1596+
}
1597+
else
1598+
fprintf(pset.queryFout,
1599+
_("The command has no result, or the result has no columns.\n"));
1600+
}
1601+
1602+
ClearOrSaveResult(results);
1603+
1604+
returnOK;
1605+
}
1606+
1607+
14851608
/*
14861609
* ExecQueryUsingCursor: run a SELECT-like query using a cursor
14871610
*
@@ -1627,7 +1750,9 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
16271750
break;
16281751
}
16291752

1630-
/* Note we do not deal with \gexec or \crosstabview modes here */
1753+
/*
1754+
* Note we do not deal with \gdesc, \gexec or \crosstabview modes here
1755+
*/
16311756

16321757
ntuples=PQntuples(results);
16331758

‎src/bin/psql/help.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -167,13 +167,14 @@ slashUsage(unsigned short int pager)
167167
* Use "psql --help=commands | wc" to count correctly. It's okay to count
168168
* the USE_READLINE line even in builds without that.
169169
*/
170-
output=PageOutput(122,pager ?&(pset.popt.topt) :NULL);
170+
output=PageOutput(125,pager ?&(pset.popt.topt) :NULL);
171171

172172
fprintf(output,_("General\n"));
173173
fprintf(output,_(" \\copyright show PostgreSQL usage and distribution terms\n"));
174174
fprintf(output,_(" \\crosstabview [COLUMNS] execute query and display results in crosstab\n"));
175175
fprintf(output,_(" \\errverbose show most recent error message at maximum verbosity\n"));
176176
fprintf(output,_(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
177+
fprintf(output,_(" \\gdesc describe result of query, without executing it\n"));
177178
fprintf(output,_(" \\gexec execute query, then execute each value in its result\n"));
178179
fprintf(output,_(" \\gset [PREFIX] execute query and store results in psql variables\n"));
179180
fprintf(output,_(" \\gx [FILE] as \\g, but forces expanded output mode\n"));

‎src/bin/psql/settings.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -93,7 +93,8 @@ typedef struct _psqlSettings
9393
char*gfname;/* one-shot file output argument for \g */
9494
boolg_expanded;/* one-shot expanded output requested via \gx */
9595
char*gset_prefix;/* one-shot prefix argument for \gset */
96-
boolgexec_flag;/* one-shot flag to execute query's results */
96+
boolgdesc_flag;/* one-shot request to describe query results */
97+
boolgexec_flag;/* one-shot request to execute query results */
9798
boolcrosstab_flag;/* one-shot request to crosstab results */
9899
char*ctv_args[4];/* \crosstabview arguments */
99100

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1433,7 +1433,7 @@ psql_completion(const char *text, int start, int end)
14331433
"\\e","\\echo","\\ef","\\elif","\\else","\\encoding",
14341434
"\\endif","\\errverbose","\\ev",
14351435
"\\f",
1436-
"\\g","\\gexec","\\gset","\\gx",
1436+
"\\g","\\gdesc","\\gexec","\\gset","\\gx",
14371437
"\\h","\\help","\\H",
14381438
"\\i","\\if","\\ir",
14391439
"\\l","\\lo_import","\\lo_export","\\lo_list","\\lo_unlink",

‎src/test/regress/expected/psql.out

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,91 @@ more than one row returned for \gset
126126
select 10 as test01, 20 as test02 from generate_series(1,0) \gset
127127
no rows returned for \gset
128128
\unset FETCH_COUNT
129+
-- \gdesc
130+
SELECT
131+
NULL AS zero,
132+
1 AS one,
133+
2.0 AS two,
134+
'three' AS three,
135+
$1 AS four,
136+
sin($2) as five,
137+
'foo'::varchar(4) as six,
138+
CURRENT_DATE AS now
139+
\gdesc
140+
Column | Type
141+
--------+----------------------
142+
zero | text
143+
one | integer
144+
two | numeric
145+
three | text
146+
four | text
147+
five | double precision
148+
six | character varying(4)
149+
now | date
150+
(8 rows)
151+
152+
-- should work with tuple-returning utilities, such as EXECUTE
153+
PREPARE test AS SELECT 1 AS first, 2 AS second;
154+
EXECUTE test \gdesc
155+
Column | Type
156+
--------+---------
157+
first | integer
158+
second | integer
159+
(2 rows)
160+
161+
EXPLAIN EXECUTE test \gdesc
162+
Column | Type
163+
------------+------
164+
QUERY PLAN | text
165+
(1 row)
166+
167+
-- should fail cleanly - syntax error
168+
SELECT 1 + \gdesc
169+
ERROR: syntax error at end of input
170+
LINE 1: SELECT 1 +
171+
^
172+
-- check behavior with empty results
173+
SELECT \gdesc
174+
The command has no result, or the result has no columns.
175+
CREATE TABLE bububu(a int) \gdesc
176+
The command has no result, or the result has no columns.
177+
-- subject command should not have executed
178+
TABLE bububu; -- fail
179+
ERROR: relation "bububu" does not exist
180+
LINE 1: TABLE bububu;
181+
^
182+
-- query buffer should remain unchanged
183+
SELECT 1 AS x, 'Hello', 2 AS y, true AS "dirty\name"
184+
\gdesc
185+
Column | Type
186+
------------+---------
187+
x | integer
188+
?column? | text
189+
y | integer
190+
dirty\name | boolean
191+
(4 rows)
192+
193+
\g
194+
x | ?column? | y | dirty\name
195+
---+----------+---+------------
196+
1 | Hello | 2 | t
197+
(1 row)
198+
199+
-- all on one line
200+
SELECT 3 AS x, 'Hello', 4 AS y, true AS "dirty\name" \gdesc \g
201+
Column | Type
202+
------------+---------
203+
x | integer
204+
?column? | text
205+
y | integer
206+
dirty\name | boolean
207+
(4 rows)
208+
209+
x | ?column? | y | dirty\name
210+
---+----------+---+------------
211+
3 | Hello | 4 | t
212+
(1 row)
213+
129214
-- \gexec
130215
create temporary table gexec_test(a int, b text, c date, d float);
131216
select format('create index on gexec_test(%I)', attname)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp