Thread: [PATCH] Add result_types column to pg_prepared_statements view
Hi hackers,
Prompted by a question on IRC, here's a patch to add a result_types
column to the pg_prepared_statements view, so that one can see the types
of the columns returned by a prepared statement, not just the parameter
types.
I'm not quite sure about the column name, suggestions welcome.
- ilmari
From 5045cd5a173fefb5346ed81d355ba35c1c922105 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Thu, 19 May 2022 15:54:56 +0100
Subject: [PATCH] Add result_types column to pg_prepared_statements view
Containing the types of the columns returned by the prepared
statement.
Prompted by question from IRC user mlvzk.
---
doc/src/sgml/catalogs.sgml | 12 +++++
src/backend/commands/prepare.c | 19 +++++--
src/include/catalog/pg_proc.dat | 6 +--
src/test/regress/expected/prepare.out | 72 +++++++++++++--------------
src/test/regress/expected/rules.out | 3 +-
src/test/regress/sql/prepare.sql | 12 ++---
6 files changed, 73 insertions(+), 51 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index d96c72e531..ae7627ae48 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -11492,6 +11492,18 @@
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>result_types</structfield> <type>regtype[]</type>
+ </para>
+ <para>
+ The types of the columns returned by the prepared statement in the
+ form of an array of <type>regtype</type>. The OID corresponding
+ to an element of this array can be obtained by casting the
+ <type>regtype</type> value to <type>oid</type>.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>from_sql</structfield> <type>bool</type>
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 80738547ed..7c8537fbd2 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -683,8 +683,16 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
hash_seq_init(&hash_seq, prepared_queries);
while ((prep_stmt = hash_seq_search(&hash_seq)) != NULL)
{
- Datum values[7];
- bool nulls[7];
+ Datum values[8];
+ bool nulls[8];
+ TupleDesc result_desc;
+ Oid *result_types;
+
+ result_desc = prep_stmt->plansource->resultDesc;
+ result_types = (Oid *) palloc(result_desc->natts * sizeof(Oid));
+
+ for (int i = 0; i < result_desc->natts; i++)
+ result_types[i] = result_desc->attrs[i].atttypid;
MemSet(nulls, 0, sizeof(nulls));
@@ -693,9 +701,10 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
values[2] = TimestampTzGetDatum(prep_stmt->prepare_time);
values[3] = build_regtype_array(prep_stmt->plansource->param_types,
prep_stmt->plansource->num_params);
- values[4] = BoolGetDatum(prep_stmt->from_sql);
- values[5] = Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
- values[6] = Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
+ values[4] = build_regtype_array(result_types, result_desc->natts);
+ values[5] = BoolGetDatum(prep_stmt->from_sql);
+ values[6] = Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
+ values[7] = Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87aa571a33..cb953c6411 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8025,9 +8025,9 @@
proname => 'pg_prepared_statement', prorows => '1000', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'record',
proargtypes => '',
- proallargtypes => '{text,text,timestamptz,_regtype,bool,int8,int8}',
- proargmodes => '{o,o,o,o,o,o,o}',
- proargnames => '{name,statement,prepare_time,parameter_types,from_sql,generic_plans,custom_plans}',
+ proallargtypes => '{text,text,timestamptz,_regtype,_regtype,bool,int8,int8}',
+ proargmodes => '{o,o,o,o,o,o,o,o}',
+ proargnames => '{name,statement,prepare_time,parameter_types,result_types,from_sql,generic_plans,custom_plans}',
prosrc => 'pg_prepared_statement' },
{ oid => '2511', descr => 'get the open cursors for this session',
proname => 'pg_cursor', prorows => '1000', proretset => 't',
diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 3306c696b1..faf07f620b 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -1,9 +1,9 @@
-- Regression tests for prepareable statements. We query the content
-- of the pg_prepared_statements view as prepared statements are
-- created and removed.
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
- name | statement | parameter_types
-------+-----------+-----------------
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
+ name | statement | parameter_types | result_types
+------+-----------+-----------------+--------------
(0 rows)
PREPARE q1 AS SELECT 1 AS a;
@@ -13,10 +13,10 @@ EXECUTE q1;
1
(1 row)
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
- name | statement | parameter_types
-------+------------------------------+-----------------
- q1 | PREPARE q1 AS SELECT 1 AS a; | {}
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
+ name | statement | parameter_types | result_types
+------+------------------------------+-----------------+--------------
+ q1 | PREPARE q1 AS SELECT 1 AS a; | {} | {integer}
(1 row)
-- should fail
@@ -32,26 +32,26 @@ EXECUTE q1;
(1 row)
PREPARE q2 AS SELECT 2 AS b;
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
- name | statement | parameter_types
-------+------------------------------+-----------------
- q1 | PREPARE q1 AS SELECT 2; | {}
- q2 | PREPARE q2 AS SELECT 2 AS b; | {}
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
+ name | statement | parameter_types | result_types
+------+------------------------------+-----------------+--------------
+ q1 | PREPARE q1 AS SELECT 2; | {} | {integer}
+ q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
(2 rows)
-- sql92 syntax
DEALLOCATE PREPARE q1;
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
- name | statement | parameter_types
-------+------------------------------+-----------------
- q2 | PREPARE q2 AS SELECT 2 AS b; | {}
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
+ name | statement | parameter_types | result_types
+------+------------------------------+-----------------+--------------
+ q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
(1 row)
DEALLOCATE PREPARE q2;
-- the view should return the empty set again
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
- name | statement | parameter_types
-------+-----------+-----------------
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
+ name | statement | parameter_types | result_types
+------+-----------+-----------------+--------------
(0 rows)
-- parameterized queries
@@ -159,24 +159,24 @@ PREPARE q6 AS
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
PREPARE q7(unknown) AS
SELECT * FROM road WHERE thepath = $1;
-SELECT name, statement, parameter_types FROM pg_prepared_statements
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements
ORDER BY name;
- name | statement | parameter_types
-------+------------------------------------------------------------------+----------------------------------------------------
- q2 | PREPARE q2(text) AS +| {text}
- | SELECT datname, datistemplate, datallowconn +|
- | FROM pg_database WHERE datname = $1; |
- q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double
precision",boolean,smallint}
- | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+|
- | ten = $3::bigint OR true = $4 OR odd = $5::int) +|
- | ORDER BY unique1; |
- q5 | PREPARE q5(int, text) AS +| {integer,text}
- | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +|
- | ORDER BY unique1; |
- q6 | PREPARE q6 AS +| {integer,name}
- | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; |
- q7 | PREPARE q7(unknown) AS +| {path}
- | SELECT * FROM road WHERE thepath = $1; |
+ name | statement | parameter_types
| result_types
+------+------------------------------------------------------------------+----------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------
+ q2 | PREPARE q2(text) AS +| {text}
| {name,boolean,boolean}
+ | SELECT datname, datistemplate, datallowconn +|
|
+ | FROM pg_database WHERE datname = $1; |
|
+ 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}
+ | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+|
|
+ | ten = $3::bigint OR true = $4 OR odd = $5::int) +|
|
+ | ORDER BY unique1; |
|
+ q5 | PREPARE q5(int, text) AS +| {integer,text}
|
{integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
+ | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +|
|
+ | ORDER BY unique1; |
|
+ q6 | PREPARE q6 AS +| {integer,name}
|
{integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
+ | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; |
|
+ q7 | PREPARE q7(unknown) AS +| {path}
| {text,path}
+ | SELECT * FROM road WHERE thepath = $1; |
|
(5 rows)
-- test DEALLOCATE ALL;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index fc3cde3226..7ec3d2688f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1423,10 +1423,11 @@ pg_prepared_statements| SELECT p.name,
p.statement,
p.prepare_time,
p.parameter_types,
+ p.result_types,
p.from_sql,
p.generic_plans,
p.custom_plans
- FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans,
custom_plans);
+ FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, result_types, from_sql,
generic_plans,custom_plans);
pg_prepared_xacts| SELECT p.transaction,
p.gid,
p.prepared,
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index 985d0f05c9..55eef91d2a 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -2,12 +2,12 @@
-- of the pg_prepared_statements view as prepared statements are
-- created and removed.
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
PREPARE q1 AS SELECT 1 AS a;
EXECUTE q1;
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
-- should fail
PREPARE q1 AS SELECT 2;
@@ -18,16 +18,16 @@ PREPARE q1 AS SELECT 2;
EXECUTE q1;
PREPARE q2 AS SELECT 2 AS b;
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
-- sql92 syntax
DEALLOCATE PREPARE q1;
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
DEALLOCATE PREPARE q2;
-- the view should return the empty set again
-SELECT name, statement, parameter_types FROM pg_prepared_statements;
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
-- parameterized queries
PREPARE q2(text) AS
@@ -71,7 +71,7 @@ PREPARE q6 AS
PREPARE q7(unknown) AS
SELECT * FROM road WHERE thepath = $1;
-SELECT name, statement, parameter_types FROM pg_prepared_statements
+SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements
ORDER BY name;
-- test DEALLOCATE ALL;
--
2.30.2
Re: [PATCH] Add result_types column to pg_prepared_statements view
From
Dagfinn Ilmari Mannsåker
Date:
Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> writes: > Hi hackers, > > Prompted by a question on IRC, here's a patch to add a result_types > column to the pg_prepared_statements view, so that one can see the types > of the columns returned by a prepared statement, not just the parameter > types. Added to the 2022-07 commitfest: https://commitfest.postgresql.org/38/3644/ - ilmari
On 19.05.22 17:34, Dagfinn Ilmari Mannsåker wrote: > Prompted by a question on IRC, here's a patch to add a result_types > column to the pg_prepared_statements view, so that one can see the types > of the columns returned by a prepared statement, not just the parameter > types. > > I'm not quite sure about the column name, suggestions welcome. I think this patch is sensible. I see one issue: When you describe a prepared statement via the protocol, if a result field has a domain as its type, the RowDescription message sends the underlying base type, not the domain type directly (see SendRowDescriptionMessage()). But it doesn't do that for the parameters (see exec_describe_statement_message()). I don't know why that is; the protocol documentation doesn't mention it. Might be worth looking into, and checking whether the analogous information contained in this view should be made consistent.
Re: [PATCH] Add result_types column to pg_prepared_statements view
From
Dagfinn Ilmari Mannsåker
Date:
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> On 19.05.22 17:34, Dagfinn Ilmari Mannsåker wrote:
>> Prompted by a question on IRC, here's a patch to add a result_types
>> column to the pg_prepared_statements view, so that one can see the types
>> of the columns returned by a prepared statement, not just the parameter
>> types.
>> I'm not quite sure about the column name, suggestions welcome.
>
> I think this patch is sensible.
>
> I see one issue: When you describe a prepared statement via the
> protocol, if a result field has a domain as its type, the RowDescription
> message sends the underlying base type, not the domain type directly
> (see SendRowDescriptionMessage()). But it doesn't do that for the
> parameters (see exec_describe_statement_message()). I don't know why
> that is; the protocol documentation doesn't mention it. Might be worth
> looking into, and checking whether the analogous information contained
> in this view should be made consistent.
A bit of git archaeology shows that the change was made by Tom (Cc-ed)
in 7.4:
commit d9b679c13a820eb7b464a1eeb1f177c3fea13ece
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2003-05-13 18:39:50 +0000
In RowDescription messages, report columns of domain datatypes as having
the type OID and typmod of the underlying base type. Per discussions
a few weeks ago with Andreas Pflug and others. Note that this behavioral
change affects both old- and new-protocol clients.
I can't find that discussion in the archive, but someone did complain
about it shortly after:
https://www.postgresql.org/message-id/flat/D71A1574-A772-11D7-913D-0030656EE7B2%40icx.net
I think in this case returning the domain type is more useful, since
it's easy to get from that to the base type, but not vice versa.
The arguments about client-side type-specific value handling for
RowDescription don't apply here IMO, since this view is more
user-facing.
- ilmari
On 01.07.22 14:27, Dagfinn Ilmari Mannsåker wrote: > Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: > >> On 19.05.22 17:34, Dagfinn Ilmari Mannsåker wrote: >>> Prompted by a question on IRC, here's a patch to add a result_types >>> column to the pg_prepared_statements view, so that one can see the types >>> of the columns returned by a prepared statement, not just the parameter >>> types. >>> I'm not quite sure about the column name, suggestions welcome. >> >> I think this patch is sensible. > The arguments about client-side type-specific value handling for > RowDescription don't apply here IMO, since this view is more > user-facing. I agree. It's also easy to change if needed. Committed as is.
Re: [PATCH] Add result_types column to pg_prepared_statements view
From
Dagfinn Ilmari Mannsåker
Date:
On Tue, 5 Jul 2022, at 06:34, Peter Eisentraut wrote: > On 01.07.22 14:27, Dagfinn Ilmari Mannsåker wrote: >> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: >> >>> On 19.05.22 17:34, Dagfinn Ilmari Mannsåker wrote: >>>> Prompted by a question on IRC, here's a patch to add a result_types >>>> column to the pg_prepared_statements view, so that one can see the types >>>> of the columns returned by a prepared statement, not just the parameter >>>> types. >>>> I'm not quite sure about the column name, suggestions welcome. >>> >>> I think this patch is sensible. > >> The arguments about client-side type-specific value handling for >> RowDescription don't apply here IMO, since this view is more >> user-facing. > > I agree. It's also easy to change if needed. Committed as is. Thanks!
On 05.07.22 09:31, Dagfinn Ilmari Mannsåker wrote: > On Tue, 5 Jul 2022, at 06:34, Peter Eisentraut wrote: >> On 01.07.22 14:27, Dagfinn Ilmari Mannsåker wrote: >>> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: >>> >>>> On 19.05.22 17:34, Dagfinn Ilmari Mannsåker wrote: >>>>> Prompted by a question on IRC, here's a patch to add a result_types >>>>> column to the pg_prepared_statements view, so that one can see the types >>>>> of the columns returned by a prepared statement, not just the parameter >>>>> types. >>>>> I'm not quite sure about the column name, suggestions welcome. >>>> >>>> I think this patch is sensible. >> >>> The arguments about client-side type-specific value handling for >>> RowDescription don't apply here IMO, since this view is more >>> user-facing. >> >> I agree. It's also easy to change if needed. Committed as is. > > Thanks! There was a problem that we didn't cover: Not all prepared statements have result descriptors (e.g., DML statements), so that would crash as written. I have changed it to return null for result_types in that case, and added a test case.
Re: [PATCH] Add result_types column to pg_prepared_statements view
From
Dagfinn Ilmari Mannsåker
Date:
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: > There was a problem that we didn't cover: Not all prepared statements > have result descriptors (e.g., DML statements), so that would crash as > written. D'oh! > I have changed it to return null for result_types in that case, and > added a test case. Thanks for spotting and fixing that. - ilmari