Re: [PATCH] Add result_types column to pg_prepared_statements view
Peter Eisentraut 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
Re: [PATCH] Add result_types column to pg_prepared_statements view
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 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
On Tue, 5 Jul 2022, at 06:34, Peter Eisentraut wrote: > On 01.07.22 14:27, Dagfinn Ilmari Mannsåker wrote: >> Peter Eisentraut 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!
Re: [PATCH] Add result_types column to pg_prepared_statements view
On 01.07.22 14:27, Dagfinn Ilmari Mannsåker wrote: Peter Eisentraut 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
Peter Eisentraut 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 Date: 2003-05-13 18:39:50 + 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
Re: [PATCH] Add result_types column to pg_prepared_statements view
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
Dagfinn Ilmari Mannsåker 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
[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?=
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 @@
+
+
+ result_types regtype[]
+
+
+ The types of the columns returned by the prepared statement in the
+ form of an array of regtype. The OID corresponding
+ to an element of this array can be obtained by casting the
+ regtype value to oid.
+
+
+
from_sql bool
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(_seq, prepared_queries);
while ((prep_stmt = hash_seq_search(_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
+--+---+--
