Set Returning Functions (SRF) - request for patch review and comment

Started by Joe Conwayover 23 years ago10 messages
#1Joe Conway
mail@joeconway.com
1 attachment(s)

I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. "SELECT tbl.* FROM myfunc() AS tbl"). I think I'm at
a good point to get review and comments. Not everything yet has been
implemented per my proposal (see:
http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
support is in place.

How it currently works:
-----------------------
1. At this point, FROM clause SRFs are used as a row source in a manner
similar to the current API, i.e. one row at a time is produced without
materializing.

2. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.

3. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the function returns a base data type, the
single result column is named for the function. If the function returns
a composite type, the result columns get the same names as the
individual attributes of the type.

4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.

5. example:
test=# CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 16693 1
test=# INSERT INTO foo VALUES(1,2,'Ed');
INSERT 16694 1
test=# INSERT INTO foo VALUES(2,1,'Mary');
INSERT 16695 1
test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
test=# SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)

test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
fooname
---------
Joe
(1 row)

test=# select * from dblink_get_pkey('foo') as t1;
dblink_get_pkey
-----------------
fooid
foosubid
(2 rows)

What still needs to be done:
----------------------------
1. Add a new table_ref node type - DONE
2. Add support for three modes of operation to RangePortal:
a. Repeated calls -- DONE
b. Materialized results -- partially complete
c. Return query -- I'm starting to wonder how/if this is really
different than a.) above
3. Add support to allow the RangePortal to materialize modes a and c,
if needed for a re-read -- partially complete.
4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
specified -- not yet started.

Request for help:
-----------------
So far I've tested with SQL and C functions. I will also do some testing
with PLpgSQL functions. I need testing and feedback from users of the
other function PLs.

Review, comments, feedback, etc. are appreciated.

Thanks,

Joe

Attachments:

srf.2002.05.05.2.patch.gzapplication/x-gzip; name=srf.2002.05.05.2.patch.gzDownload
�,�<srf.2002.05.05.2.patch�=�s�6�?3��ke���,�R_r�s��=�N-�������$$��H����u����@�_�+��{��V�\,v�==�y�D�6o�#����N��&��?<t_�z�����1���f�H�8�{/����4W�o���y���g,��G!k�_x�t�\v�O������fZV��sY����vo���z���>88X���a�������0������w�N��Qb�����/�,���L���OK?�]'R���9g����c��������������Q�8�D��C {����������G������U�v�v��#'L��{�������q���;b	��������5P
�N��k�xI����'��5���n�?8<<2q=���#
���,�&�;>w�q��A0�O&�k�?�c���������[�)���c�^�����ztqzs~u�;��R�����f�������k����e�����0��>�%j���kPG�x��Jw�l����5
l`{��_�{�Z�O���f�]	������p�on�Q��b�2`�q2����[��V��	1.P �m�w�r�F�$p0�n{�]l��gy\�_��ea���&�/�8����T���C_h"j����i�������]+�	�o�0ez
I2��t# ����u��9(`�+��%"9�l%�D�z���)v(��'�r��	9��1��pvN��Hb�� ��"vL	��Q���
5�w��J]��~��|�jY=
��"���(Zd�O�e�sK��\�G��}�og��'����c���.?\\�Y+MT	H�A�w7�.(�	�����4pQ���6�:��K@�w�����\�&+��%<9A�\s���v:�
��?��r�xK�X�\��,*y�;���^�toU6{.|� �{����D���g�d���K[hg���R������9l:�b������(����]���I����c��(�������at��-��J@�N��w3�@�a��"����:���	����_8���2��L4�j�c��T�����@������WD&�~���l���2���;�#����MVJ���*��\��>f)��T���j��1_`��f���7��i����J��2�v���e����8"���_�4��h�����"�~�+������9*~�C����a?�)�U�c6S�~r�]�n�5�CD�!$!�['+�'�g\���@t�ED�BA^sW��O�1O�4��`�����@.�������.o�������1����_b�Fr�:A0d`J���&T)6e���MF%�9Y^�Fr,KN��Yr\e�������+2��k�\���r��h�5s)��+gVj0DuVfP���C?�s��� ���s��}�2�Z�����O~�KH�v�aR
��Y��`
L��_h��*|JW	�������n����[m��6�O���EP�]s�
��(��q8�2���3����R�Vf8�l��d +����#+
V8�n�c������'�v{�1�8���V��EZ����G���_����])�2���;)�"{6`F�slw�3:]�@iq�$!����%0�I\����6�*���O�W�i�~�T�+�7��|��o1����X�X�cv���q�h���1�_i��}M���n�����<�Z�5`����G��q�#U���8�o� YI84r��J�**W_k�n�`g8�/����s����4v>y��[z��Q���H���l���}��s�o1�jw��M�=��T���lY����B0�c�T|��F�0�!�8�CzY% �e������F�`l���w���,��.<���&��XN���'�
K`�j8uZ��� ���Y����I�Oe�d��l���(��u?� `�\j����t>���4!�)�'Hgr>����&h���c4�
/)>i
�e��O9}]�!M�]��$GX)v���^���>��gw�=G&� #�8P����C���/���<�8���.�A������	�a�����s�q�0��*D.D�h;��+��y�w�;R!DHD-�!�y�M�<B�������b�0x�p�����=hz�q4��Z[&DI=`�����
�(����h0���C�T8�T������Zn��P����q���~�������h�������
)}[��f�
��6��(���G*��QaI�r�����xt1:�Qf
�A3��\�]*f���q���]D���K�����DK��������|&Ig�6��$�����}x��m�Bs����.j���'��E����R\��ll��V6$I��XL�G�����pQ	��{z8��(|��a�4l�3�q���7������f�\�*qI���(+��&Y0,�Jnl2��\=�c3l�[��"�x�����k���\���F�������c�G-�i�PK���?�����$7_@<�OK�'��A���eq0�8�����~�o�?���x������Wg�E���X���0a�Q���f3`�Xps��q�vMF��uq��0��� i�E����
��"	z��������t���>�Q2���hf\:�7�|u~Yh����^����-��l���Rd�����J����Fb(�8���n'�����(L[	w$�gD<V�����=��>p1T�����*E0������JC���d��9�T���X��l�'79=�q�i�:�e[w
�Cm�S�����2��\�������������*�|R`T�����^����&u7�\V����,K%[qFKB��,�<dVwX��d��l�������*Ek�4�:@�V�lGC����w�9Znx�Fp>gQ��1����N�|�3�o�P�yT����n�~]�DM���>��R���l�_���9���/�I\���4g<��X.�Fh*���a.Y�e	tL����mR����FEFD�fE�$�W�9a�WnWdpV���@��9���h0��[0����v-}��2�\�����}�0���~����M�d�4������>6����h�N�g������E?� j��1���s�l�3Q���)8!��Y�7:8��-co�����v��v�r3���u[V�d{��:vw`��r���G���p�9�t��2L�A��
����7��Ws����N2.��}�����Bo�BWh/q��=�o����<joq����Y4�G!v�qY�=4R�B1������I�����]R���+&��F��P*O��9c��.YCu��4�{�O�Ym��s�t���t���Z�?h�\����W�{	�;����z�V7u��}��W8��������a��`M����K6�.����}��I�s2B�{'��M�zJ���6��3Ue�����<d�Q��|q�	�&
9�{������|Y����M�q7���<A��Y��
�����%HZ�#���1�������?�����{'��N\7lq�|W�+�F�5�e�s��z��I�`�������aO����%|�+������0=��6@�����U�KH��� �sW��(r|���������}r20�{<�/=�9�9!��S/0�	��	�����>��
�mN�[Q��w��m{������rCh������!��j������-�j
�e��<Bc�1���ak��/�O��!��>X��*C������Aw�Z��e-��x����V	��/�
�H��c��N��7��q�@���cL������0��B$�%��\����	�~��h��0:%�������n&b�2�{?y�0j>�#���2����������m�i&��'�x<�<R)%�����6���V�vUI�y4��w0�X�?g�lW�MR5�����������ps~9��Z��cH���xX�k s!�8�N$V����bk�����Bc�.��>�������<6����^�a#�G,������V!�2��7%�4O�9Z&�f�����)dU$��+g�r��y���3/veN�@�&����V[���o��5S�@�O��������M�Hb^"U���gq��*:Z�.����m���{I�px����Cr���j��x_G�M�YXL��^�Br�[�+�<&p�S���Y]��TD����h��vJk	@���Iv�&R��.%Q��
(,T�|7�@1V\���sV�C�V�������MD�����LZ$�#�)o+������T:3� �;_�����7�"0&����'U����j�A7�Z�*�X�^�e���n�0����RW�R�Y�Q�!��B-7b�v����b%���W���G���J��k����u���D�F����%WC�G/����!wv���:�d`��	X��Z��&���y�M�?{I�-���E����)9�M�a52L@���������Na��S>D7��4�M].�Y��vI2-�bEML�.�V�N�J�HX.&yYk���K*
zb�����Z��tfUZ^��^WI� ���6�����/$dC|��P
Lo!H9Li���W'�e���W�L��������FMJ�/<���N�d��*��D�yry��<n4g��1�R�k�#Uc:�z��{b�R�B$A�YmV}��������(N��q�]p�����s��!x��W\��v�VM���sI���FQ��,��s`V6�l��iz�-#_�
?s'�3��`r��N	�I[?���zpB<p��P�a�������T�Y�<����Y=��u�n�?������v_��v���L��8����QY�|�u,)���e7����p�A�	�yB'd$�H]���5��h$v~R^1
������4�&+G�Q�hJ�UD���F����9P5����,�1$��e��F#�O��w��k���f����[���+���KI�-/����$9$r��.n���*|ZExV���#�4��HOu���A���3^h���������;`C���j�^��+�p�������C������0\����?�G��{�d����#UF=i�RB-K5��Xq�R�I������_b6UxN��*<3��U����rL��Sx�$��T����z���G-EV���U����@��Y��(t���NI�FX�����R�5B��"����N/'�����1�	�u;yiV�)��;��(I�����S�CeE���J�9R����3�!Ry���������^�Pn~Gb��g�u�Q_�0����������*	�)g%:j+5����<dt%/���2�����A�����"x��s����!#>�����F�����Fp�Q���?f���4����Y�R����-��ZAa�7
]���W)�I�
������;p+��QEo����O�X�pk����'��)m}�b:�D��o���2�}��Xo��3��T�+�P���g��P�O�f�S��u��ba>���8�����$�3��p�[B�N��������y�����09I����%��9�1C���W����\CY�4I��%5�em���mCmv��.)�3[�1+�����{��!�:��L�-d�7�������TV]-��7Qv�	�E>�Xm�%��q���j����=o��:�Q��&*d�<VW�W��#��EZ��.�WK���Y9����f�xnC���tM����k�������t��p�q<��*��?�i�u�(�������6��/zmR��c� L�����nPA&����[�7�������{k�K����F�/pqsJV���~�NB�.���xB�K�^�K4�����e9��C��Q��H"��
�DN�n�w�@�#9�����a�?g�f�9h�_X��l����
������N�Y���@���d��&��fD�=X���)��;�,���|-c�_����> ��T�$�h��t����Ou5�����3-��,�d��
#&���z#�L����BH�N�|C���
��m�*����S��Z�y�?A��#Ol�z�\���z�VTx*�P�����^e��
�
�F���Nkb�$����iR�T����������K:��hrs�M����I�����L�
X����7-n���eG�r%u�i{x �0!	��h�����[��H�����6����w�e����"�h���v1�:��o��\��p���W��:
��3a�L~YE�o�;��
`cb�Q��$8�������LlPB��;�����3����0��#�y��<�o����l��������|u/����My����v��a��H�FZ�5�L����VD��#i�y�s��U	q�����c9���^��}���e��5h�#K���f����%����E�.i����0U��_�j��U��p!����AFG����t���hi�� 8)��L��g���l��V������:ciVrvY���j���_Ym"����/�"�z;�ro��Q�3�L47xw����s���3�R�[:�X�_�6�A`V+xW��,��)S�^ ���L��81!���s�aSG�����N�_�?���@������.�+k�����j�?�,��P$�Y�Yn�5|�s�)W����%p�e��o���<��}'w���6��i%������_����h	b�q#����� ��b����G����>�?�p�����68�%<��Z�/5�6�`.�����m���AUa�L(���u<9\_��G�-v*�>��&����&0������=�;,)�Y �CZ�K��*�T�,�L�C��MJ�[7v�M�hd����0���db�l
���2�u��>J�{K:@�AW�qE<��
�`�����2�U���8;O�x`����E���gU�%M�t}�43U�������[�C��C�-����1���f�]���	������X���|��T!�u�Kg�V�qR$�����	�7�<Q_�F��8Q%g�Hq��h�%������t���Y����*#�
��E0�|B��B3�T��j���j��j�������A��B!M��V��w��;,��p$>_3�0���������H�
���F��?������(��(#���f��y2w�?�����#-�)~	"��|@���&�@y������t7��!v�[��������E3y���0��/�#�6��HWhq�A��
v��;O�`Z$9=&W~�a�y������Y��&��+��o��{�|/'�e�l��X_{��ft/��#\]��1�*�]����%F�����9_�,MSu�����C��12\��[rf085*<�z+rIhF����������G���8����+��Wx�%��d��t��&c�2��;w;������{<QOUV��_���G��i��B�D�X�rb��?�0�?/k����v$8#4o���w�"��TnL�x��8C���Nl!<{N�@���X�.\"����������etP=O����tG���@n4�f3X	�O���\Mi� ����a��P���A�P�:���uE��Ih�[R_�a:-�u	-~�#���<%o��si��sRe�,w��^�%O`v�n$�������2b�f��>mDBNK����`1�[�k���i���6����>*�=h��I6���p�G���W�����O�J�y�J�����Qx��S�|u��TQT���$U��/������$#���
���a���:���[������/�Kh�(�lh��z�v���MUHXo������L��|�N8��^=k�_VL�Kl��fR��������k������p��
�9��������Dp��~��O����V
�v�i��v��D!Mz�U��N�k�������x��C�������$�x������������	��?�����\$_��9f�H��X���x���k�Y��5
�����S��3����������*�9�����RjB�+���h.�
C�(����a�^T��������Wr��?g������n/�?D��(j��Qu
��e���iB|�^�y��i��C����Kg���-�BR�;���3z�32���/�{W3�_n���-�gM-��N7�?D������o���c���Y�vG��3�D�}�_����r�q���p�^�C�6�RCe��-��'���[)�H7��/[Q���x�����v5���^;�?d���hS@wWcH��D�2�������An�M��o����K��Q���z� L��k��D�����4�+^���mug��B�c 
��{�h��f���t�ix'1
��~,M�,z4H��T�#����;|������I����d����dN'��wn�j�����{�E��gmbf�I���~������@�O�_��uKu��������MVp��j�GO
\����I���M�8u�U�����dq���3���-�>n���5V
�3�AKz'���R���[�[��&C�HU)A�
��h�����3S��F��\�_0?e��������Zm�`�]Ae([K���h�<�z7��g"�w�f��p�3�{������Lr�*cA��3������8@�?�<��T:��v������~�E�����v��r�TW�"`�U'��|�pX_�w\���I���h��D�85�`��^�����+{������d-�����	@7�'����<�����`��@�i��C�a5��Y;�[�^/��s���XV��1h�d�
g�s��U�����=�����b�q�m,�����s��Si���}����c��������s�;!�@fV~�~N��!�d;�����m�0�DF�����mfs��yHF�V�
;�8��#���y�����Kz}�]��r�-m���"[6�Bh�Q�%�1���Q�<�	�fc�M���3���.���T�����3~����|2T��^�P�0�o(U����!����������/��c��VUS��|�����5���^F@���eRN��cM����� v]_�?9����cGP[���f��~��W7�2����f�S��%�	W��b
�NF�q'T��Mo�����sUH sn��`�XtJ�Ss'�������<��N�x~~ur��F��<��OG�o�W���FO��)�u�I��r�)�D�eYHs�?���i�W������Y��b��;��0n@�a���<��F��C��V��������r+���Y3C�����P�O��8g�ch��EF�
�df�����Yi>�^�g��z���.��|{no�g���T���I����X�}�W��f��U
v�zF�_�t�;�������
V(�u���a3F���s���=�������6�T��g��4cQ�#�:��	u@l����y�X��P�x�y-l�&5���v�l��xj[�A�|�z�G��
8�vl�����]�c���H#L��aF�>2�|P����VFe��m����v�����Z��������fE��z��eX�Z�m����U��.��b[Z��
��8����������#k}M����3��z�f�R��S<���pt�q�&]
����
(|d
W*�5��G�h����� B�VG����=������gg��W.��Vy�(!�I�l��3��d��b�]<�PC�R�~�!�^�'�'�m�����vo���m�Y��O ���Q�N�v}����]�z��Z�����iTZ�$,9���j:�(2����������9�aKAr�!�
J��������#g���oC�c��8�>����O�l=:^V:�
~l�_�����V��P{=�V�o�T.����wr����S�Ft���T��6XH��*�l�7�s��O�"���9��'�d,��}u$��bj{�1:�
��=�q������Q��]V��
����4�����M���_����U���Bo����+��Z|�:=|�������/��AZ}��JM�X&uk�9��"\��>&y�>�f���
�P��KC���������N�X!u������H��=��V�Z;���q-�icw<w8�+���av��P{����E��J�)�n'���J;���{9E0>t�S�������!po����z�;���#�)-���P�Z�f���h'�K����a�����VK���ia'�.���������'y�o~�?k�@?���@��
�����+P�\���[c}u��+��:g`�>� �e^n������w
8�|%g�wq�e�~�E����"�s�y9���!��>��Q�3k���D�u&n�X
kL[~�?��(�?��Y{�
�vv��C
�B�9�iJ������P�:
��Of�a�����/�o�j�rD��	%,��
(x��pOk���vu=��`�����?��������w�<���$��Y\���c����1����gv�;\��{�������&9��N�G���e����;����>�"�����O���#��D��d|4+H���|�0�b����f��:o����<�H4�����H��Qh����*E9�����(#7qhC\:z|����`�}c*�Y�Qk{	h����|��a��^���:�&s��s���OJY��5A5q!������Xm�����+M�k|)Y���O��sz��T�\gF:���4%�[�J2O�y�t<
�a����%�W]���t�������=���G�^9�8�\������c�M��op{�l���J.������H1��ck������m����1�����rp%� �Uq������O9!"Z1�����0H�sa0��`�7^M����sg�Jf�wtx����F:c����7���v�����A����#��<Q�z���x�9�'��Vt�%�2L
���?���N��0�)If����[q��I�3�J�����(��M���b�N�i�\1�3|:��v���p��|!�#��������f�t�l�Z����~"�	�bV�x���5gT^�����]a(4����[�L�WM
��R����S��<?0������=����_AtE��@�������u!�AV]����|�Q���1Jt����yMl��x�i����h�9��:T����X��c��������_��W���'1���&�7B�<���V@F���Wd���oP��O�.���W���T�H�����������KaZ6���������{�p���
�k#������*�E��Mxk�~��M)�N���4$37y
.��r��.jB���1fB&>G%&�39/��=�`2J�LI���iN6	W��i�d�I�������5%3�0M� ��}^R�}�}U1S�O�(V@66�t�r5e6;u�.�(���n�P��D�ed��h q:�)tq�3�;�����I�E���I)���Q��z�V�������P�3�����Y���8�B��x\3����{I�6n9���y��yV^(������?�6+�p��C�j��-,q`������
L,�fA(������D�$����dtgK0Zy�&�h)R�t����
{Bl����#`��K,��}B����`[q��H�1�Y5?�y�@|�uQ�4!�0���5#x�U�8O���I��(�krxy��F�)��iZ�C�7��u;� ��ue�xBZ�Z����a������Xi�$�o=��I��g�`_A�c�_�=��D��##V;�n��zH����	�����^��������?u���
�VT���MSu��K;�N�l��j��}vW<��f(��8W���a �������+�-fl��� �}86.�����P�-<SS��xb��DuF�����}���,����zb�����&�X�/|�J���(��R�q���9������
I�z��[���?'#�	�����n:��S�9''=��	�qZvf�({��[�����(����R����Z��']2���[u�H�jO��vY���e�y��f�;�����!mJ�������x/W��H������[�����H/�����1�eTRUl�
c�T;����0�L�Ji���Z�ap����R>)���I����M�g��7��\����J�@r�1Tj����s�
����W*����E$��&O���������|�%���Zz���u%�Uw�����i���1���?��q�5w��<�xH��3�Z�|���� ���o/g�M���8����
,/�n�G;�Q�L��L������F���|1G{�5�(��2�`��V����f���[D�[�l���"�y�nr���I�[^#���)���)�����j����4�UK�����':&��o��G�?����r��n�m����t���]�
e�}]m���;{�p}��DT���=��p���g��r1�
7�,0�]��(�Y������������r��W
��hC
�^���R?���<���s����y�����������rWCC\/s��;��^��FB��`E�c������g���g��Q8��f����('P����;�z~b���@�}���-=)p�=�QV]	�����~?�
D
z��
���c�M>����|���9���l:R��NT�
c�O�.���^���O���FngQ�/��M�Nm������t�I�Jj��U�`���/������(�W^W?%7�v���K�P�Ly���	~���+:qJi�W�~*p��[D��HX�~�~�L��X��J��fE�'����t�jgP�w����������%A+���T\g������&��oK�Y�"���$����|�0
=���e��+Q��h��AQ����a��^�9����Fl����8�ju��T-02.�U�|i����*��&?�$�:M�G�(9��$2��Zk�i"*����p*�*��O������G$�^�3�����x|������k!��u�\�;�s.�v���� �]�JFv0��������h��j=fs^�l�������uW��G���>�yuz�d��;�I�g�����,��L���s�U�x�c����O�&<w�����-jR��im���.z]w���/ ��w��N���+�Ra�b���5���N1JP8��(�zp����2�����b�p�fy�.�������j�Q��<�<�y��C� ���n$S���@��c��F�Qv;�(���Nk���z�@y�,�}?�%6�w��"?�jc����}��?����JwtM0������J�>����
�����>W�J����zu�����dP�z
G��z�]����r����-?GV@��I���M�������hx�Du�/l"g�.m�oa?��k�����D=�u��4��z2�M��y�
�=���&�/8F��OT}]���P^u'|OeC�QBzr�0�Z�����4���d��P~h�Y�>c�#L)�B�Q�ai�k���h3�A�"��<�.����r����5�I���t��������\�z��Ie@��	����)���9p���H�S�{���l�L�)�D\Z�I��kQ�o)����z�rVA
���7h�$u�8��������u��B���Q�7�`����2�F�(�N&���w����D��'X����uW�`�*����jX���[�D���G�K�>�-������� |h��PeL��j�4[��^��6-3����jn��Q4z|�@�d��K2�S���-oR$_����w��v�����AHJ��p��g�{��%�bi/��a/�u�{�^������K���
CX��4����~�6x�|<i=)c<��X]��E����������q��7Q�iXQk���K�A��NL�n����������p��Zg�Y�7�/�l�}%�TJNK�#O��#U�Tg����[�
����Q{��^�V���_�^>�p�N�������k�����n�6�j�/*����L�?�,����{�\�F��"�$7?����LU:��(������U������?nS��������S�(��-�+rQ2��C`&�}�S;_v���b�1^�c�s-
q�����e2B���1�	�����v���@p��H_B�����3Z���?�0���*'�7P�f����@uqJ��76G�I���W�s��ey���oT/���'�M������������qz����)QuM)
1-t�#&*�>����s^7���-��y1V}>�C y�_���p�D@�=P�����>�i�!p5���A/���^�5��m��B����(6/�V3�&��A��m1FR����'��!��m��6���%������9t���b^���0A_�{@nR��D�������T�E�U��������+���2��mF�
�;_�D�Xv�<[������?����{F�%�ss���^�2�=E��q����/�C�]�!|@����� ��*m��nc���i�3�K��Z�������g^��ff:�\��T�Y�a��4���$��ewi��������lb�&��O	�
�9#��0��&$d���*:����N)j;MT��"*��}���PvUaZ�!��3m=���CG@��N14t��s��9���������R�a�%��TptvX$�
l�1�.	�(�~��E]OvP�>�:fI�f���,�����E9g�����P���|����-7��2<)�)"l���!E��K���o�6[pP�`��J8A���	O`I&�R^��N��|�`�m�F����������WuE����=K���
�S�����U�5�2�n��,j�N��y�X+(38�S�nc?F�;������F��X�+%���8��c�����#�����1��4�bv�?�+����Z&]2��-��\�OI��������)����6gwM^=�i�� ����tdz��:]7�;�����,����
1i,����N�A
v�4�
*�_��l���g)��lh�"��:]hcI�E�0{K���Z���]��aO+�k���Eb����p���^��]l����N7�z���^���I�kN��T�%E�+�T�;�4l���pH�&�nT��f+H~>Of����
r;,�"U��B��)�FT�������}U����2����2���+oZ�O���j�E��TkK��2[:��[����R�����qLU��H"��.&�x�|�u��4�gnI/zt���e@��C�*8���:9�8��\������+����IJfBSR-�R%5��(����
����lu�[�:G;T�V��z!�s�R+��be�dn��������#sK�YL9w,O�9����x�g�zpXwK���e���X�b|C&C��R.�8T?�����[<KJ�J�������w�j�w����!�)ce$L�!G�J��o���8�H�"��#E�6-L1v��x�K������6��z9x�(�����5m�pq���>L�-q)���XZ���9�u�v9%j��:�"�lV�K�N����=�'�7#����:�_2��D%��N��Y���=+�g#V��n��?W��������iWo���f.���Q���R�,�n��)@�M��*�'����l6au�M��[5g����8���D�u8/T�\�7��B`�D�H��d"b8�]��S<�8��x����m���v��#uxt�J��ySg�av�|�������.+NT7���������s,����EzAT��Tg�"MD
A19�O*���Bw;�N(g#��f����P�,���y�!��
����A�kj����-���������N�P����63�~���A�-fM\�I1�\F���������=`�H�]�B���G%�c�De����N~UJ�i�\1% }4��(49GV�b�Hz���8CP��G}fv�� �R�.�,03�yS��!��(����EZ|�Ob��$������n��t���Y�p������nq�2>�`�M����c���KF[��5���XL�vw�^(a�c��G���oKV�'������'S_d^�����1�S�O�����[��F.��+�������"1?S����2�W?�1��:�w��O���&�s�TV{@��U����igG"�������#�T�N�N����9}rf��9��V�	�5NsK7;�99���z8D6��5��WV�������?�]8+i����
.:;=�`hp%z��1��_���|���|o��"9�*��������-��r��y��s�$\������WXe�O�L$d��Q��]a�B����	t3��C���%(/�X�])����I�/��#��k]y�d�1��s��4(I�^��n/�����X�����Q��,�j�.��Ym4�+���z�`vv��|j-���w �4����u����3�p4��S"Se A���<�p��=��}&9���t��iZA��j>��7{\�r��	�w�Y�9�����s��[�.��#Lky38�����O�F�C���M]K��)J�S���.����iqe>pJh��M�.[�����1\�����J��%����~[f�_�f`�I��pX[CY}G��
�Di��g��x��SG�tP�
p2~�7pSnc��R+�R����Q�ii����D�c7���f�_��X�a�)�.���BP�Q��
�t�R�4Zpu���=��c�*��:
�u�=w��9��J��1m�#�fz.eof����	�����O����N���o{s�� `Wd��m��F�w��K��W���=I��:3^=���Q�o���j�|D�2����5������f��+��
�����n�_��_�V�q�0��hP�].�-��Z;Ux�����u��j)�6����`C��U�tR`|�����S�M.�^��J��5����b�Z���guW���}��bs�����(�gJA�,(��X��K�YN�E�,�V���<���."�T�ukG���7����6&��g�[�,����a)3�c�����6L����Mk��e��W�-�k3��W�l�����wa�]V{]f�>8�9|xV�Nk�����!��U��,���o��j&\0	��q]�iq�}<�F���D����g}'~|%G^��=\��|yz�.����
8��.4��^.'�_�����5���8�/u���P'I���������7����bc+���_�d�7�.}
����aM���m����Yj
���n�����~v���H���������;5��2����,�Z�o��l�Pl��������F
z#,�~�b������9�{A��z��A�r������A�2BU�)���4�S2C��$�����he-��h�����cKq�)X��F��MX�������+��
�V���4���D��0xVy��I�H�'xY�v[�z��m)�����#������-E������u�N3w���r��:��t��
�������:�}2)����:<�s�(�h�V��
�j�za�M�D���ep(�����4t��1�hj��Sn��F�Q�eZt;�7�0��F73�r�q/��5�h�9c�F������E?)=�`�F1����r�b13�^ X�$�����&����W����A&�X��m}����I�=�����
���a�VE�����{����@ZU*�&�o����?�P�,�x@I3�Od�k���
|�t�J%��Q����v�(zy+[�������?�>�Y��W������ ��r#XNt������Y��� �QcB���A�l_�j�~Sv���_���H����/����y�
n#>5��A5d�ZZ8�	6�)^��B�b���w��h���n������du�����\4lYJ^PjID��68H��`�C��L	���\����4����(r�Kz8��}��o���Q�U�*?��'�U�t���c��}e�����q�������u���tom����9�o-������OoA���k��zjx{���
�o��&Y��J�"�G�<Z��v$�NDr-2����^2v�`��C������D����?,�h"Q��*�X�L��	�+%)5�p��z�b
zqG�Q���6$c-��.�XB5��.��g!v�$\9�p��(��L*��YrN!���(�6}�[*>5��h�b�bR?��gQiV*A�(����a�������D������]��\PP�x=��'k�<� *����"�~�l�5������fM���/'M�����IS���^���IS�*IS���,iJww7���L����h��[[�����J7�)��*�������b���q`�����dp�m����������B���V_���h���v�Y�g=kw��g��%��!�a#�Da��~���?kW+����a]�^7�e44� X|:>���������#n����-�Vn���������4> �@��8�0�'Jq��~��?���r�ZM�t^ �I5q��fr��U����s�[%����_����Re����J|�G7�5����j�Mz�u��
��6��$�VEq���-��#����C]P:��N���e�sN
l��d��|������r{����7�e��^S��@��B�0����_�f���Nw��:�)f����9e>���i6}&���L/�&u�����hm�����^:��;��p�o�|�=)���������!�X�2��s�������)lF��������������e��������kwe
jz��$���a$,�T�Qg��m5-���g?��I�8�}H�����x�������V�8hMu��O�S�P���
�>���
~��!�*�T�5��T���b��q�2�*�}`��c���_�������rJ��Fi>c�%�I�9Vbi�&��Q���������4���@�MGX��"��zE^�2}*M�3�9#��y���M�����-�:Z2�D��$���|���+�=R@`g�k�����4��etCyt��;Q����*?�����dsJ����~~u�q�1�}\�5�������b<nD���D{;m,wH�S�I���=6�dbo�������7Q�&;�$!����G6�ge���h������lv�E���-����3�B����'X���8;\�=���O{7��U]���'��<�1�����y�7��u<N��N�X�����>�A�fx�g p}�d�"�^��A����w��$��w��'�olO������-��{q|<zs�����������:�W���gN�_���x!�O��S���8�m�,d��7�?�mS�B}���l0��	%7�O�i���08PYW��i���@���v����Mg��PZ���c��|�o���E-]3y~]�\x��g�b�E�����#�xP)"_����^i����r�����������g��A�����o�����
��� �l�������U������������pL,L��C@1��\dL$B�yLx:���F`!�/Z3������f
�,���� �D�0���0�y��cB}��OP�sS�h�e�S���CV�^}�sa	y��,��9]����h����yr��-���R�Sf��
�������M�qZ��l�s�e����%����%�{�]�+���7dg�sPo��@d���=O?�ns/$-�H9DEjq8G�'hR���g�����@�������z��z�_�)a0���E�= ��yQ=��l2��v��D��&�&��r��<�/�l�����-�������rxOG�&��q_1�=E���)�f�<�4L���������6U��sa6&���
�������y�Qj��H��Y_/�\i�I���h"�A����YAP���6��� ��KXk�,���|��C@G ���!jIO�y2��9���m@@��<f�t��GFJV
Y�vRg����S�������L���*so�����
&��U�?j�K��~���k�����h?��
���6�e�8�H��k��i:B\�w��Z?Z|>*:|���4���?����N�$h=�����s��#�v+v�0�U/Js91�;�yR�����$-NL�9e��?����SOz�,�s'���hG�����N[��WR��������T3�^}@)1�V��kBu���kr${�\��7��l<�5�������]�����J6Y^���*�����,>@%��_K%<_����R�5�))����[a�%:{$
IG"��>-���u��2������Gqa�S���rV�B�x\��������rf��7s'�a���	K=O�9�&������n������&|}K�b��
"���OP��D���>w��U��1<�88���:��\6i	����+I�B����"
����
m��r����G�|:x
�~KI�a�Q�j�o~<��CG�����_Q��?
�|`��H����Y��oe��FW���Aw����7�����#Q�1�	�]S�2~���s5��8�9����@�����29�0�Z�T^�	�����9��=���9���}���5cq�x^h5������a�!
�S�/�k
-\��z6�qi�E]���^����b^�����,f���UxJ� ���v��i�K�y/�'�g�����*1����k�n���@���kh�m����k)��aC��	�����Fw��0�(�I..��	*�r����tt����9������m�?�7����Un�aJ������pA��5�R��*��N��yZ�N*�����)
��X{H���\X������+2w�\���YU	h�y��O�VW�^G�T�i�Fob����������M&e�1�������s!�]��b��\p;|��:r�Q ']s�m��b��
�����6��}���������Wv�?o����&D�*�t:M��7���������o�P��
�rOy2��E�.� �d�_!u�*BtkS��#���8E,�n����>�	M��y��.ph	�t��R�d��P]e\�YL�`F���SJ�Zs?�����(����&G��+���c��<cZd�g��"�ZD���������K��7���e<
H\���������	r�����Qb�e�	�g����}'��[R����_@��E��� �;���m �����P�sMQ�A�v�z��u1n7S�#\��u]����~�#���k?�lZ�F{������bi��3�:t��5��J7����;�X���B���`,�N���
@;�:E�<F���q6�)�L(}Sc�a&m��:��:b�w��<���
(��k��J��������R�D�
#��7���^��g!�����a��8n����s���W���Xy�K�k�
�y�H{������w^qK2�sX�L�L�7��r�<�
����C
	���A�^��k?��#*q�;����R����^��l;�z�vm{�@L��m#&�Qd��(�l(]�������t�m0t�aU�+�O�����9�{�.�C�L��^�X��K���/oO��8_Za��;B�������Sx0��,�Yo����\!'�,= ���kD\�q�i/\�h�N�j6�v���x7�J�p�Y��g����")>�38v:r	�R��1o��e���'�p
-k�II�l�<�Xp�X���`����?T���~U�?���\re�NsN���1�}�y�����)��M����E��%�	~Jn��h���c>������L��u]�������Q����gnR�J����^�RG�C9�
�a��6�������~�p�jLJ�/��V����|/��pq��T�=�Z���!�)K�����%���<�`�i�s�w����b���7�1Z�I��<����P��
#2Christopher Kings-Lynne
chriskl@familyhealth.com.au
In reply to: Joe Conway (#1)
Re: Set Returning Functions (SRF) - request for patch review and comment

Feedback: you're a legend!

I'll try to patch my CVS and test it at some point...

Chris

Show quoted text

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Joe Conway
Sent: Tuesday, 7 May 2002 12:51 AM
To: pgsql-hackers
Subject: [HACKERS] Set Returning Functions (SRF) - request for patch
review and comment

I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. "SELECT tbl.* FROM myfunc() AS tbl"). I think I'm at
a good point to get review and comments. Not everything yet has been
implemented per my proposal (see:
http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
support is in place.

How it currently works:
-----------------------
1. At this point, FROM clause SRFs are used as a row source in a manner
similar to the current API, i.e. one row at a time is produced without
materializing.

2. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.

3. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the function returns a base data type, the
single result column is named for the function. If the function returns
a composite type, the result columns get the same names as the
individual attributes of the type.

4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.

5. example:
test=# CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 16693 1
test=# INSERT INTO foo VALUES(1,2,'Ed');
INSERT 16694 1
test=# INSERT INTO foo VALUES(2,1,'Mary');
INSERT 16695 1
test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
test=# SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)

test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
fooname
---------
Joe
(1 row)

test=# select * from dblink_get_pkey('foo') as t1;
dblink_get_pkey
-----------------
fooid
foosubid
(2 rows)

What still needs to be done:
----------------------------
1. Add a new table_ref node type - DONE
2. Add support for three modes of operation to RangePortal:
a. Repeated calls -- DONE
b. Materialized results -- partially complete
c. Return query -- I'm starting to wonder how/if this is really
different than a.) above
3. Add support to allow the RangePortal to materialize modes a and c,
if needed for a re-read -- partially complete.
4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
specified -- not yet started.

Request for help:
-----------------
So far I've tested with SQL and C functions. I will also do some testing
with PLpgSQL functions. I need testing and feedback from users of the
other function PLs.

Review, comments, feedback, etc. are appreciated.

Thanks,

Joe

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Joe Conway (#1)
Re: Set Returning Functions (SRF) - request for patch review and comment

Joe Conway <mail@joeconway.com> writes:

I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. "SELECT tbl.* FROM myfunc() AS tbl"). I think I'm at
a good point to get review and comments.

A few random comments ---

4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.

This seems unnecessary; couldn't we use the function name as the default
alias name? The reason we require an alias for a subselect is that
there's no obvious automatic choice for a subselect; but there is for a
function.

You may not want to hear this at this point ;-) but I'd be strongly
inclined to s/portal/function/ throughout the patch. The implementation
doesn't seem to have anything to do with portals as defined by
portalmem.c, so using the same name just sounds like a recipe for
confusion. (I think Alex started with that name because he intended
to allow fetches from cursors --- but you're not doing that, and if
someone were to add it later he'd probably want to use the name
RangePortal for that.)

The patch's approach to checking function execute permissions seems
wrong, because it only looks at the topmost node of the function
expression. Consider
select ... from myfunc(1, sin(x))
Probably better to let init_fcache do the checking instead when the
expression is prepared for execution.

*** src/backend/executor/execQual.c	27 Apr 2002 03:45:03 -0000	1.91
--- src/backend/executor/execQual.c	5 May 2002 21:36:55 -0000
***************
*** 44,49 ****
--- 44,52 ----
  #include "utils/fcache.h"
+ Datum ExecEvalFunc(Expr *funcClause, ExprContext *econtext,
+ 			 bool *isNull, ExprDoneCond *isDone);
+ 

(and a corresponding "extern" in some other .c file) Naughty naughty...
this should be in a .h file. But actually you should probably just be
calling ExecEvalExpr anyway, rather than hard-wiring the assumption that
the top node of the expression tree is a Func. Most of the other places
that assume that could be fixed easily by using functions like
exprType() in place of direct field access.

I've been toying with eliminating Iter nodes, which don't seem to do
anything especially worthwhile --- it'd make a lot more sense to add
a "returnsSet" boolean in Func nodes. Dunno if that simplifies life
for you. If you take the above advice you may find you don't really
care anymore whether there's an Iter node in the tree.

ExecPortalReScan does not look like it works yet (in fact, it looks like
it will dump core). This is important. It also brings up the question
of how you are handling parameters passed into the function. I think
there's a lot more to that than meets the eye.

I have been thinking that TupleStore ought to be extended to allow
fetching of existing entries without foreclosing the option of storing
more tuples. This would allow you to operate "on the fly" without
necessarily having to fetch the entire function output on first call.
You fetch only as far as you've been requested to provide answers.
(Which would be a good thing; consider cases with LIMIT for example.)

Good to see you making progress on this; it's been a wishlist item
for a long time.

regards, tom lane

#4Joe Conway
mail@joeconway.com
In reply to: Joe Conway (#1)
Re: Set Returning Functions (SRF) - request for patch review

Tom Lane wrote:

4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.

This seems unnecessary; couldn't we use the function name as the default
alias name? The reason we require an alias for a subselect is that
there's no obvious automatic choice for a subselect; but there is for a
function.

Yeah, I was on the fence about this. The only problem I could see is
when the function returns a base type, what do I use for the column
alias? Is it OK to use the same alias for the relation and column?

You may not want to hear this at this point ;-) but I'd be strongly
inclined to s/portal/function/ throughout the patch. The implementation
doesn't seem to have anything to do with portals as defined by
portalmem.c, so using the same name just sounds like a recipe for

I was already thinking the same thing. It will be a real PITA, but I do
believe it is the right thing to do.

The patch's approach to checking function execute permissions seems
wrong, because it only looks at the topmost node of the function
expression. Consider
select ... from myfunc(1, sin(x))
Probably better to let init_fcache do the checking instead when the
expression is prepared for execution.

OK.

+ Datum ExecEvalFunc(Expr *funcClause, ExprContext *econtext,
+ 			 bool *isNull, ExprDoneCond *isDone);
+ 

(and a corresponding "extern" in some other .c file) Naughty naughty...
this should be in a .h file. But actually you should probably just be
calling ExecEvalExpr anyway, rather than hard-wiring the assumption that
the top node of the expression tree is a Func. Most of the other places
that assume that could be fixed easily by using functions like
exprType() in place of direct field access.

OK.

I've been toying with eliminating Iter nodes, which don't seem to do
anything especially worthwhile --- it'd make a lot more sense to add
a "returnsSet" boolean in Func nodes. Dunno if that simplifies life
for you. If you take the above advice you may find you don't really
care anymore whether there's an Iter node in the tree.

Actually it gets in my way a bit, and I think I remember some
discussions wrt removing it. But I wasn't sure how large the impact
would be on the current API if I messed with it, so I thought I'd leave
it for now. Do you think it's worth it to address this now?

ExecPortalReScan does not look like it works yet (in fact, it looks like
it will dump core). This is important. It also brings up the question
of how you are handling parameters passed into the function. I think
there's a lot more to that than meets the eye.

Yeah, I took a first shot at writing the rescan support, but have not
yet begun to use/test it. I'd like to get the rest of the patch to an
acceptable level first, then concentrate on get materialization and
rescan working.

I have been thinking that TupleStore ought to be extended to allow
fetching of existing entries without foreclosing the option of storing
more tuples. This would allow you to operate "on the fly" without
necessarily having to fetch the entire function output on first call.
You fetch only as far as you've been requested to provide answers.
(Which would be a good thing; consider cases with LIMIT for example.)

Hmm, I'll have to look at this more closely. When I get to the
materialization/rescan stuff, I'll see if I can address this idea.

Thanks for the review and comments!

Joe

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Joe Conway (#4)
Re: Set Returning Functions (SRF) - request for patch review and comment

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:

This seems unnecessary; couldn't we use the function name as the default
alias name? The reason we require an alias for a subselect is that
there's no obvious automatic choice for a subselect; but there is for a
function.

Yeah, I was on the fence about this. The only problem I could see is
when the function returns a base type, what do I use for the column
alias? Is it OK to use the same alias for the relation and column?

Sure. foo.foo is valid for a column foo in a table foo, so I don't
see a problem with it for a function.

You may not want to hear this at this point ;-) but I'd be strongly
inclined to s/portal/function/ throughout the patch.

I was already thinking the same thing. It will be a real PITA, but I do
believe it is the right thing to do.

You could try doing the text substitution on the diff file and then
re-applying the diff to fresh sources. Might get a couple of merge
failures, but should be a lot less painful than doing the edit directly
on the full sources.

I've been toying with eliminating Iter nodes, which don't seem to do
anything especially worthwhile --- it'd make a lot more sense to add
a "returnsSet" boolean in Func nodes. Dunno if that simplifies life
for you. If you take the above advice you may find you don't really
care anymore whether there's an Iter node in the tree.

Actually it gets in my way a bit, and I think I remember some
discussions wrt removing it. But I wasn't sure how large the impact
would be on the current API if I messed with it, so I thought I'd leave
it for now. Do you think it's worth it to address this now?

Up to you; probably should wait to see if Iter is still in your way
after you do the other thing. I think removing it and instead inserting
returnsSet booleans in Oper and Func nodes would be a pretty
straightforward exercise, but it'll mean touching even more stuff.
Might be best to do that as a separate patch.

ExecPortalReScan does not look like it works yet (in fact, it looks like
it will dump core). This is important. It also brings up the question
of how you are handling parameters passed into the function. I think
there's a lot more to that than meets the eye.

Yeah, I took a first shot at writing the rescan support, but have not
yet begun to use/test it. I'd like to get the rest of the patch to an
acceptable level first, then concentrate on get materialization and
rescan working.

Fair enough. We should try to get the bulk of the patch applied soon
so that you don't have code drift problems. The rescan issues should
not involve touching nearly as much code.

regards, tom lane

#6Joe Conway
mail@joeconway.com
In reply to: Joe Conway (#1)
1 attachment(s)
SRF patch (was Re: [HACKERS] Set Returning Functions (SRF) - request for patch review and comment)

Tom Lane wrote:

Sure. foo.foo is valid for a column foo in a table foo, so I don't
see a problem with it for a function.

Fixed

You could try doing the text substitution on the diff file and then
re-applying the diff to fresh sources. Might get a couple of merge
failures, but should be a lot less painful than doing the edit directly
on the full sources.

Great idea! Turned out to be a relatively painless 10 minute exercise.

Up to you; probably should wait to see if Iter is still in your way
after you do the other thing. I think removing it and instead inserting
returnsSet booleans in Oper and Func nodes would be a pretty
straightforward exercise, but it'll mean touching even more stuff.
Might be best to do that as a separate patch.

I'd like to wait on this -- I'm already drinking from a firehose ;-)

Fair enough. We should try to get the bulk of the patch applied soon
so that you don't have code drift problems. The rescan issues should
not involve touching nearly as much code.

I also fixed the execute permissions, switched from ExecEvalFunc to
ExecEvalExpr, and fixed a bug that I found in _outRangeTblEntry (which
was preventing creation of a VIEW using a RangeFunction). If this could
be applied it would definitely help -- it's getting hard to keep it in
sync with cvs due to its size. The patch applies cleanly to cvs tip as
of a few minutes ago, and passes all regression tests.

Thanks,

Joe

Attachments:

srf.2002.05.07.2.patch.gzapplication/x-gzip; name=srf.2002.05.07.2.patch.gzDownload
#7Joe Conway
mail@joeconway.com
In reply to: Joe Conway (#1)
1 attachment(s)
Re: SRF patch (was Re: [HACKERS] Set Returning Functions

Joe Conway wrote:

Tom Lane wrote:

Sure. foo.foo is valid for a column foo in a table foo, so I
don't see a problem with it for a function.

Fixed

Sorry -- when I fixed this, I introduced a new bug which only shows for
functions returning composite types, and of course I tested one
returning a base type :(

If you do apply the last srf patch, please apply this one over it.

Thanks,

Joe

Attachments:

parse_relation.patchtext/plain; name=parse_relation.patchDownload
*** pgsql/src/backend/parser/parse_relation.c	Tue May  7 21:54:14 2002
--- pgsql.1/src/backend/parser/parse_relation.c	Tue May  7 21:44:13 2002
***************
*** 738,750 ****
  		 */
  		Relation	rel;
  		int			maxattrs;
- 		int			numaliases;
  
  		rel = heap_open(funcrelid, AccessShareLock);
  
- 		eref = (Alias *) copyObject(alias);
- 		numaliases = length(eref->colnames);
- 
  		/*
  		 * Since the rel is open anyway, let's check that the number of column
  		 * aliases is reasonable.
--- 738,746 ----
***************
*** 777,791 ****
  		 * Must be a base data type, i.e. scalar.
  		 * Just add one alias column named for the function.
  		 */
! 		char	   *attrname;
! 		Func	   *func;
! 		Oid			funcid;
! 
! 		func = (Func *) (expr->oper);
! 		funcid = func->funcid;
! 		attrname = get_func_name(funcid);
! 
! 		eref->colnames = lappend(eref->colnames, makeString(attrname));
  		rte->eref = eref;
  	}
  
--- 773,779 ----
  		 * Must be a base data type, i.e. scalar.
  		 * Just add one alias column named for the function.
  		 */
! 		eref->colnames = lappend(eref->colnames, makeString(funcname));
  		rte->eref = eref;
  	}
  
#8Ian Barwick
barwick@gmx.net
In reply to: Joe Conway (#1)
Re: Set Returning Functions (SRF) - request for patch review and comment

On Monday 06 May 2002 18:51, Joe Conway wrote:
(...)

Request for help:
-----------------
So far I've tested with SQL and C functions.

(...)

Can you post an example of a function in C?
(I'm trying out your patch from Friday).

Thanks,

Ian Barwick

#9Joe Conway
mail@joeconway.com
In reply to: Joe Conway (#1)
Re: Set Returning Functions (SRF) - request for patch review

Ian Barwick wrote:

On Monday 06 May 2002 18:51, Joe Conway wrote:
(...)

Request for help:
-----------------
So far I've tested with SQL and C functions.

(...)

Can you post an example of a function in C?
(I'm trying out your patch from Friday).

Thanks,

Ian Barwick

See contrib/dblink. The version in cvs HEAD has two that return sets --
dblink() which returns an int, and dblink_get_pkey() which returns text.
I don't have an example that returns a composite type though. I'll make
one of those for testing some time next week.

Joe

#10Ian Barwick
barwick@gmx.net
In reply to: Joe Conway (#9)
Re: Set Returning Functions (SRF) - request for patch review

See contrib/dblink. The version in cvs HEAD has two that return sets --
dblink() which returns an int, and dblink_get_pkey() which returns text.

Thanks, now I can see what I was doing wrong

Yours

Ian Barwick