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

Commit84ad713

Browse files
committed
Add result_types column to pg_prepared_statements view
Containing the types of the columns returned by the preparedstatement.Prompted by question from IRC user mlvzk.Author: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>Discussion:https://www.postgresql.org/message-id/flat/871qwpo7te.fsf@wibble.ilmari.org
1 parenteb64cea commit84ad713

File tree

7 files changed

+74
-52
lines changed

7 files changed

+74
-52
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11502,6 +11502,18 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
1150211502
</para></entry>
1150311503
</row>
1150411504

11505+
<row>
11506+
<entry role="catalog_table_entry"><para role="column_definition">
11507+
<structfield>result_types</structfield> <type>regtype[]</type>
11508+
</para>
11509+
<para>
11510+
The types of the columns returned by the prepared statement in the
11511+
form of an array of <type>regtype</type>. The OID corresponding
11512+
to an element of this array can be obtained by casting the
11513+
<type>regtype</type> value to <type>oid</type>.
11514+
</para></entry>
11515+
</row>
11516+
1150511517
<row>
1150611518
<entry role="catalog_table_entry"><para role="column_definition">
1150711519
<structfield>from_sql</structfield> <type>bool</type>

‎src/backend/commands/prepare.c

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -683,8 +683,16 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
683683
hash_seq_init(&hash_seq,prepared_queries);
684684
while ((prep_stmt=hash_seq_search(&hash_seq))!=NULL)
685685
{
686-
Datumvalues[7];
687-
boolnulls[7];
686+
TupleDescresult_desc;
687+
Oid*result_types;
688+
Datumvalues[8];
689+
boolnulls[8];
690+
691+
result_desc=prep_stmt->plansource->resultDesc;
692+
result_types= (Oid*)palloc(result_desc->natts*sizeof(Oid));
693+
694+
for (inti=0;i<result_desc->natts;i++)
695+
result_types[i]=result_desc->attrs[i].atttypid;
688696

689697
MemSet(nulls,0,sizeof(nulls));
690698

@@ -693,9 +701,10 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
693701
values[2]=TimestampTzGetDatum(prep_stmt->prepare_time);
694702
values[3]=build_regtype_array(prep_stmt->plansource->param_types,
695703
prep_stmt->plansource->num_params);
696-
values[4]=BoolGetDatum(prep_stmt->from_sql);
697-
values[5]=Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
698-
values[6]=Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
704+
values[4]=build_regtype_array(result_types,result_desc->natts);
705+
values[5]=BoolGetDatum(prep_stmt->from_sql);
706+
values[6]=Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
707+
values[7]=Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
699708

700709
tuplestore_putvalues(rsinfo->setResult,rsinfo->setDesc,
701710
values,nulls);

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202205191
56+
#defineCATALOG_VERSION_NO202207051
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8025,9 +8025,9 @@
80258025
proname => 'pg_prepared_statement', prorows => '1000', proretset => 't',
80268026
provolatile => 's', proparallel => 'r', prorettype => 'record',
80278027
proargtypes => '',
8028-
proallargtypes => '{text,text,timestamptz,_regtype,bool,int8,int8}',
8029-
proargmodes => '{o,o,o,o,o,o,o}',
8030-
proargnames => '{name,statement,prepare_time,parameter_types,from_sql,generic_plans,custom_plans}',
8028+
proallargtypes => '{text,text,timestamptz,_regtype,_regtype,bool,int8,int8}',
8029+
proargmodes => '{o,o,o,o,o,o,o,o}',
8030+
proargnames => '{name,statement,prepare_time,parameter_types,result_types,from_sql,generic_plans,custom_plans}',
80318031
prosrc => 'pg_prepared_statement' },
80328032
{ oid => '2511', descr => 'get the open cursors for this session',
80338033
proname => 'pg_cursor', prorows => '1000', proretset => 't',

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

Lines changed: 36 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
-- Regression tests for prepareable statements. We query the content
22
-- of the pg_prepared_statements view as prepared statements are
33
-- created and removed.
4-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
5-
name | statement | parameter_types
6-
------+-----------+-----------------
4+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
5+
name | statement | parameter_types| result_types
6+
------+-----------+-----------------+--------------
77
(0 rows)
88

99
PREPARE q1 AS SELECT 1 AS a;
@@ -13,10 +13,10 @@ EXECUTE q1;
1313
1
1414
(1 row)
1515

16-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
17-
name | statement | parameter_types
18-
------+------------------------------+-----------------
19-
q1 | PREPARE q1 AS SELECT 1 AS a; | {}
16+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
17+
name | statement | parameter_types| result_types
18+
------+------------------------------+-----------------+--------------
19+
q1 | PREPARE q1 AS SELECT 1 AS a; | {} | {integer}
2020
(1 row)
2121

2222
-- should fail
@@ -32,26 +32,26 @@ EXECUTE q1;
3232
(1 row)
3333

3434
PREPARE q2 AS SELECT 2 AS b;
35-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
36-
name | statement | parameter_types
37-
------+------------------------------+-----------------
38-
q1 | PREPARE q1 AS SELECT 2; | {}
39-
q2 | PREPARE q2 AS SELECT 2 AS b; | {}
35+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
36+
name | statement | parameter_types| result_types
37+
------+------------------------------+-----------------+--------------
38+
q1 | PREPARE q1 AS SELECT 2; | {} | {integer}
39+
q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
4040
(2 rows)
4141

4242
-- sql92 syntax
4343
DEALLOCATE PREPARE q1;
44-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
45-
name | statement | parameter_types
46-
------+------------------------------+-----------------
47-
q2 | PREPARE q2 AS SELECT 2 AS b; | {}
44+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
45+
name | statement | parameter_types| result_types
46+
------+------------------------------+-----------------+--------------
47+
q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
4848
(1 row)
4949

5050
DEALLOCATE PREPARE q2;
5151
-- the view should return the empty set again
52-
SELECT name, statement, parameter_types FROM pg_prepared_statements;
53-
name | statement | parameter_types
54-
------+-----------+-----------------
52+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
53+
name | statement | parameter_types| result_types
54+
------+-----------+-----------------+--------------
5555
(0 rows)
5656

5757
-- parameterized queries
@@ -159,24 +159,24 @@ PREPARE q6 AS
159159
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
160160
PREPARE q7(unknown) AS
161161
SELECT * FROM road WHERE thepath = $1;
162-
SELECT name, statement, parameter_types FROM pg_prepared_statements
162+
SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements
163163
ORDER BY name;
164-
name | statement | parameter_types
165-
------+------------------------------------------------------------------+----------------------------------------------------
166-
q2 | PREPARE q2(text) AS +| {text}
167-
| SELECT datname, datistemplate, datallowconn +|
168-
| FROM pg_database WHERE datname = $1; |
169-
q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double precision",boolean,smallint}
170-
| SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+|
171-
| ten = $3::bigint OR true = $4 OR odd = $5::int) +|
172-
| ORDER BY unique1; |
173-
q5 | PREPARE q5(int, text) AS +| {integer,text}
174-
| SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +|
175-
| ORDER BY unique1; |
176-
q6 | PREPARE q6 AS +| {integer,name}
177-
| SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; |
178-
q7 | PREPARE q7(unknown) AS +| {path}
179-
| SELECT * FROM road WHERE thepath = $1; |
164+
name | statement | parameter_types| result_types
165+
------+------------------------------------------------------------------+----------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------
166+
q2 | PREPARE q2(text) AS +| {text} | {name,boolean,boolean}
167+
| SELECT datname, datistemplate, datallowconn +| |
168+
| FROM pg_database WHERE datname = $1; | |
169+
q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double precision",boolean,smallint} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
170+
| SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+| |
171+
| ten = $3::bigint OR true = $4 OR odd = $5::int) +| |
172+
| ORDER BY unique1; | |
173+
q5 | PREPARE q5(int, text) AS +| {integer,text} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
174+
| SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| |
175+
| ORDER BY unique1; | |
176+
q6 | PREPARE q6 AS +| {integer,name} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
177+
| SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | |
178+
q7 | PREPARE q7(unknown) AS +| {path} | {text,path}
179+
| SELECT * FROM road WHERE thepath = $1; | |
180180
(5 rows)
181181

182182
-- test DEALLOCATE ALL;

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1423,10 +1423,11 @@ pg_prepared_statements| SELECT p.name,
14231423
p.statement,
14241424
p.prepare_time,
14251425
p.parameter_types,
1426+
p.result_types,
14261427
p.from_sql,
14271428
p.generic_plans,
14281429
p.custom_plans
1429-
FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans, custom_plans);
1430+
FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types,result_types,from_sql, generic_plans, custom_plans);
14301431
pg_prepared_xacts| SELECT p.transaction,
14311432
p.gid,
14321433
p.prepared,

‎src/test/regress/sql/prepare.sql

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2,12 +2,12 @@
22
-- of the pg_prepared_statements view as prepared statements are
33
-- created and removed.
44

5-
SELECT name, statement, parameter_typesFROM pg_prepared_statements;
5+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements;
66

77
PREPARE q1ASSELECT1AS a;
88
EXECUTE q1;
99

10-
SELECT name, statement, parameter_typesFROM pg_prepared_statements;
10+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements;
1111

1212
-- should fail
1313
PREPARE q1ASSELECT2;
@@ -18,16 +18,16 @@ PREPARE q1 AS SELECT 2;
1818
EXECUTE q1;
1919

2020
PREPARE q2ASSELECT2AS b;
21-
SELECT name, statement, parameter_typesFROM pg_prepared_statements;
21+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements;
2222

2323
-- sql92 syntax
2424
DEALLOCATE PREPARE q1;
2525

26-
SELECT name, statement, parameter_typesFROM pg_prepared_statements;
26+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements;
2727

2828
DEALLOCATE PREPARE q2;
2929
-- the view should return the empty set again
30-
SELECT name, statement, parameter_typesFROM pg_prepared_statements;
30+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements;
3131

3232
-- parameterized queries
3333
PREPARE q2(text)AS
@@ -71,7 +71,7 @@ PREPARE q6 AS
7171
PREPARE q7(unknown)AS
7272
SELECT*FROM roadWHERE thepath= $1;
7373

74-
SELECT name, statement, parameter_typesFROM pg_prepared_statements
74+
SELECT name, statement, parameter_types, result_typesFROM pg_prepared_statements
7575
ORDER BY name;
7676

7777
-- test DEALLOCATE ALL;

0 commit comments

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