[WIP] Inspection of row types in pl/pgsql and pl/sql
Hi
I've completed a (first) working version of a extension that allows
easier introspection of composite types from SQL and pl/PGSQL.
The original proposal and ensuing discussion can be found here:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg00695.php
The extension can be found on:
http://github.com/fgp/pg_record_inspect
This is what the extension currently provides (all in schema
record_inspect).
* fieldinfo [composite type]
Used to by fieldinfos() to describe a record's fields.
Contains the fields
fieldname (name),
fieldtype (regclass),
fieldtypemod (varchar)
* fieldinfo[] fieldinfos(record)
Returns an array of <fieldinfo>s describing the record''s fields
* anyelement fieldvalue(record, field name, defval anyelement,
coerce boolean)
Returns the value of the field <field>, or <defval> should the value
be null. If <coerce> is true, the value is coerced to <defval>'s type
if possible, otherwise an error is raised if the field''s type and
<defval>'s type differ.
* anyelement fieldvalues(record, defval anyelement, coerce boolean)
Returns an array containing values of the record'' fields. NULL
values are replaced by <defval>. If <coerce> is false, only the
fields with the same type as <defval> are considered. Otherwise, the
field'' values are coerced if possible, or an error is raised if not.
The most hacky part of the code is probably coerceDatum() - needed to
coerce a field's value to the requested output type. I wanted to avoid
creating and parsing an actual SQL statement for every cast, and instead
chose to use coerce_to_target_type() to create the expression trees
representing casts. I use the noe type CoerceToDomainValue to inject the
source value into the cast plan upon execution - see makeCastPlan() and
execCastPlan() for details. If anyone has a better idea, please speak up....
I personally would like to see this becoming a contrib module one day,
but that of course depends on how much interest there is in such a feature.
best regards,
Florian Pflug
Attachments:
smime.p7sapplication/pkcs7-signature; name=smime.p7sDownload
0� *�H��
��0�10 + 0� *�H��
���0��0��� ��0
*�H��
0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA0
090720000001Z
100720235959Z0h10U
Persona Not Validated1)0'U StartCom Free Certificate Member10 *�H��
fgp@phlo.org0�"0
*�H��
� 0�
� � ����!C9E7>_�]4�Z�y�5�f,�z��*���� `�f����W���&��_�?Z�$�����%�3K��m����"����^3��� 2�wK��%��g��N��"U�~c��H��}&\������O+4�qu���g>������Bo���Q�����C:}$/����tE��8C���i�X��2�`�,��]��>:�gu��#
�����u�#X���>'q�.`������mV��{�t����nL������f=E ���0��0 U0 0U�0U%0++0U!d��A����=M��w�k!o0U0�fgp@phlo.org0��U#��0���Sr������\|~�5N���Q�����0}10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1)0'U StartCom Certification Authority�
0�GU �>0�:0�6+��7 0�%0.+"http://www.startssl.com/policy.pdf04+(http://www.startssl.com/intermediate.pdf0��+0��0
StartCom Ltd.0��Limited Liability, read the section *Legal Limitations* of the StartCom Certification Authority Policy available at http://www.startssl.com/policy.pdf0cU\0Z0+�)�'�%http://www.startssl.com/crtu1-crl.crl0+�)�'�%http://crl.startssl.com/crtu1-crl.crl0��+��009+0�-http://ocsp.startssl.com/sub/class1/client/ca0B+0�6http://www.startssl.com/certs/sub.class1.client.ca.crt0#U0�http://www.startssl.com/0
*�H��
� ��Z��?*
����B���� ��{Yi��5\(��D���.e���B.1���lc/�F�"��
)��z��Q���f���~�x��y�{4r9�9�`YQ��i`l�����-Z�xwk�N��7����a�H���jM{B����4��K�l����/�����PkN5�=���)X��0Z�6�BJ��n�,E��I����(�������N�Sp�"S �c�~����>� �h$���'�Zsm+�pa��,�<���G��0��0��� ��0
*�H��
0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA0
090720000001Z
100720235959Z0h10U
Persona Not Validated1)0'U StartCom Free Certificate Member10 *�H��
fgp@phlo.org0�"0
*�H��
� 0�
� � ����!C9E7>_�]4�Z�y�5�f,�z��*���� `�f����W���&��_�?Z�$�����%�3K��m����"����^3��� 2�wK��%��g��N��"U�~c��H��}&\������O+4�qu���g>������Bo���Q�����C:}$/����tE��8C���i�X��2�`�,��]��>:�gu��#
�����u�#X���>'q�.`������mV��{�t����nL������f=E ���0��0 U0 0U�0U%0++0U!d��A����=M��w�k!o0U0�fgp@phlo.org0��U#��0���Sr������\|~�5N���Q�����0}10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1)0'U StartCom Certification Authority�
0�GU �>0�:0�6+��7 0�%0.+"http://www.startssl.com/policy.pdf04+(http://www.startssl.com/intermediate.pdf0��+0��0
StartCom Ltd.0��Limited Liability, read the section *Legal Limitations* of the StartCom Certification Authority Policy available at http://www.startssl.com/policy.pdf0cU\0Z0+�)�'�%http://www.startssl.com/crtu1-crl.crl0+�)�'�%http://crl.startssl.com/crtu1-crl.crl0��+��009+0�-http://ocsp.startssl.com/sub/class1/client/ca0B+0�6http://www.startssl.com/certs/sub.class1.client.ca.crt0#U0�http://www.startssl.com/0
*�H��
� ��Z��?*
����B���� ��{Yi��5\(��D���.e���B.1���lc/�F�"��
)��z��Q���f���~�x��y�{4r9�9�`YQ��i`l�����-Z�xwk�N��7����a�H���jM{B����4��K�l����/�����PkN5�=���)X��0Z�6�BJ��n�,E��I����(�������N�Sp�"S �c�~����>� �h$���'�Zsm+�pa��,�<���G��0��0���
0
*�H��
0}10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1)0'U StartCom Certification Authority0
071024210154Z
121022210154Z0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA0�"0
*�H��
� 0�
� � ���-��)�.����2����A�UG��o���#G�
��B|N�D�����Rp�M-��B��=o���-�we�5��J�Qpa>O��.�#������.���_�<���V��
[~�*��*�p�z��~�3�W�G� .�������Ml�r[�<C�e�6���f����q������O���"��u��xf�WN�#�u����i���c�gk��v$����Lb�%�������y��`�����_�{`���xK'G�N��� ��[0�W0U0�0U�0USr������\|~�5N���Q�0��U#��0���N��@[�i�0�4hC�A������0}10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1)0'U StartCom Certification Authority�0 U0 0=+10/0-+0�!http://www.startssl.com/sfsca.crt0`UY0W0,�*�(�&http://cert.startcom.org/sfsca-crl.crl0'�%�#�!http://crl.startssl.com/sfsca.crl0�]U �T0�P0�L+��70�;0/+#http://cert.startcom.org/policy.pdf05+)http://cert.startcom.org/intermediate.pdf0��+0��0' Start Commercial (StartCom) Ltd.0��Limited Liability, read the section *Legal Limitations* of the StartCom Certification Authority Policy available at http://cert.startcom.org/policy.pdf0 `�H��B 0P `�H��B
CAStartCom Class 1 Primary Intermediate Free SSL Email Certificates0
*�H��
� ����[����,0 ���&����(�)��0�����4���~�}��A�N��1� �"�O�bT� :d>������kI��:�n$�ir�+���+v���D�������
�������G��N$74���i��l*�N�5��Y4��e����h�.�|���R��e���F��8!t|{�t�/����`��8�C�Sa���� R"���������.����pi� ��|���sB��I� �Z,�&���������� ��������=Q�Csv��t���Qc:6"�i��A����y
����'��4s��Q�@@����V������0����HI��L��6��FC8��(�P�98�'��(V�7������3��W��4�lV/�K��F��QG����4�u��������,2� �l
Z���H�x�'�%:8�����:��(h���P��)��/�����j���GB����X��bgn�m�%�3��:�f� �9���<�����������_��W�1��0��0��0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA ��0 + ��0 *�H��
1 *�H��
0 *�H��
1
091220190412Z0# *�H��
1�x���]^>Tf �>�'��r5�0_ *�H��
1R0P0 `�He0
*�H��
0*�H��
�0
*�H��
@0+0
*�H��
(0�� +�71��0��0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA ��0��*�H��
1�����0��10 UIL10U
StartCom Ltd.1+0)U"Secure Digital Certificate Signing1806U/StartCom Class 1 Primary Intermediate Client CA ��0
*�H��
� ��7��2��]�1� �U�#�}��*��:g�ev"C
�R��"O�o�����i��Tf�*3�
� }:�k��\��v$K������"� Eq���+�*/E
-�����;��("������pW�e�������5;�+<