[WIP] Inspection of row types in pl/pgsql and pl/sql

Started by Florian G. Pflugabout 16 years ago1 messages
#1Florian G. Pflug
fgp@phlo.org
1 attachment(s)

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	U00U�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+��70�%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	U00U�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+��70�%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�W0U0�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	U00=+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��B0P	`�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
����'��4s��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;�+<���h�E�2=��^����zo�
@����]��z�e����
Aw�P>�DF3��>�#QV��m{�_0o3����|3o�����"�~�v�=������:�&�"+��������U