The mysterious pg_proc.protrftypes
Hi,
I'm trying to understand how pg_proc.protrftypes works.
The documentation says "Data type OIDs for which to apply transforms.".
For this column, there is no reference to any catalog table?
I would guess it should be "(references pg_type.oid)", right?
I tried to generate a value for this column to verify my hypothesis,
but I struggle to find an example that produces a not null value here.
I grepped the sources and found the "CREATE TRANSFORM FOR type_name" command,
and found an extension using it named "bool_plperl" which I installed.
I assumed this would cause a value, but no.
Both of bool_plperl's two functions get null pg_proc.protrftypes values.
I've tried running the full regression "make installcheck", but protrftypes doesn't seem to be covered:
$ cd postgresql
$ make installcheck
...
=======================
All 203 tests passed.
=======================
$ psql regression
regression=# SELECT COUNT(*) FROM pg_proc WHERE protrftypes IS NOT NULL;
count
-------
0
(1 row)
Can someone please show me how to generate a function with a not null pg_proc.protrftypes value?
Many thanks.
/Joel
po 25. 1. 2021 v 8:05 odesílatel Joel Jacobson <joel@compiler.org> napsal:
Hi,
I'm trying to understand how pg_proc.protrftypes works.
The documentation says "Data type OIDs for which to apply transforms.".
For this column, there is no reference to any catalog table?
I would guess it should be "(references pg_type.oid)", right?I tried to generate a value for this column to verify my hypothesis,
but I struggle to find an example that produces a not null value here.I grepped the sources and found the "CREATE TRANSFORM FOR type_name"
command,
and found an extension using it named "bool_plperl" which I installed.I assumed this would cause a value, but no.
Both of bool_plperl's two functions get null pg_proc.protrftypes values.
I've tried running the full regression "make installcheck",
but protrftypes doesn't seem to be covered:$ cd postgresql
$ make installcheck
...
=======================
All 203 tests passed.
=======================
$ psql regression
regression=# SELECT COUNT(*) FROM pg_proc WHERE protrftypes IS NOT NULL;
count
-------
0
(1 row)Can someone please show me how to generate a function with a not null
pg_proc.protrftypes value?
you should to use TRANSFORM clause in CREATE FUNCTION statement
https://www.postgresql.org/docs/current/sql-createfunction.html
CREATE EXTENSION hstore_plperl CASCADE;
CREATE FUNCTION test2() RETURNS hstore
LANGUAGE plperl
TRANSFORM FOR TYPE hstore
AS $$
$val = {a => 1, b => 'boo', c => undef};
return $val;
$$;
Regards
Pavel
Show quoted text
Many thanks.
/Joel
On Mon, Jan 25, 2021, at 08:14, Pavel Stehule wrote:
you should to use TRANSFORM clause in CREATE FUNCTION statement
Thanks, it worked, and like expected it references the pg_type.oid of the transform.
Attached patch adds "(references pg_type.oid)" to the documentation for pg_proc.protrftypes.
Suggested commit message: "Document the fact that pg_proc.protrftypes references pg_type.oid"
/Joel
Attachments:
pg-proc-protrftypes-references-pg-type-oid.patchapplication/octet-stream; name=pg-proc-protrftypes-references-pg-type-oid.patchDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 43d7a1ad90..9536c57426 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5945,6 +5945,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>protrftypes</structfield> <type>oid[]</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
</para>
<para>
Data type OIDs for which to apply transforms.
po 25. 1. 2021 v 8:47 odesílatel Joel Jacobson <joel@compiler.org> napsal:
On Mon, Jan 25, 2021, at 08:14, Pavel Stehule wrote:
you should to use TRANSFORM clause in CREATE FUNCTION statement
Thanks, it worked, and like expected it references the pg_type.oid of the
transform.Attached patch adds "(references pg_type.oid)" to the documentation
for pg_proc.protrftypes.Suggested commit message: "Document the fact that pg_proc.protrftypes
references pg_type.oid"
+1
Pavel
Show quoted text
/Joel
"Joel Jacobson" <joel@compiler.org> writes:
Attached patch adds "(references pg_type.oid)" to the documentation for pg_proc.protrftypes.
Agreed, pushed. I also stumbled over a backend core dump while
testing it :-(. So this whole area seems a bit spongy ...
regards, tom lane