Returning Composite Types from C functions

On Sat, Jun 18, 2005 at 09:18:34PM +1000, John Hansen wrote:

SELECT ('1:2:3:4:5'::text::my_type).*;

This results in the text_to_my_type(text) function being called no less
than 5 times. Once for each element.

Is this the desired behaviour, or a bug?

It's a known behavior with functions that return composite types.
Apparently it's not easy to fix:

http://archives.postgresql.org/pgsql-hackers/2005-04/msg00971.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr [mailto:mike@fuhr.org] Wrote:

Sent: Saturday, June 18, 2005 9:56 PM
To: John Hansen
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Returning Composite Types from C functions

On Sat, Jun 18, 2005 at 09:18:34PM +1000, John Hansen wrote:

SELECT ('1:2:3:4:5'::text::my_type).*;

This results in the text_to_my_type(text) function being called no
less than 5 times. Once for each element.

Is this the desired behaviour, or a bug?

It's a known behavior with functions that return composite types.
Apparently it's not easy to fix:

http://archives.postgresql.org/pgsql-hackers/2005-04/msg00971.php

There is a workaround tho, so should be fixable:

SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;

Or am I missing something?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

... John

On Sat, Jun 18, 2005 at 10:03:38PM +1000, John Hansen wrote:

There is a workaround tho, so should be fixable:

SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;

Or am I missing something?

I don't know enough about PostgreSQL internals so I'll have to defer
to the developers. But here's a case where the above workaround
doesn't work -- in my tests, the cast function is called once per
column per row, or ten times:

CREATE TABLE foo (t text);
INSERT INTO foo VALUES ('1:2:3:4:5');
INSERT INTO foo VALUES ('6:7:8:9:10');
SELECT (a.b).* FROM (SELECT t::my_type AS b FROM foo) AS a;

What do you get? Can you think of a workaround for this case?
Maybe one of the developers can comment on why your example calls
the function only once and mine calls it multiple times per row,
even though they look similar.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

"John Hansen" <john@geeknet.com.au> writes:

There is a workaround tho, so should be fixable:
SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;
Or am I missing something?

Try it ;-)

regards, tom lane

Yes, it worked for me,...

But my point is the workaround shouldn't be nescessary....

"John Hansen" <john@geeknet.com.au> writes:

SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS
b) AS a;
Or am I missing something?

Try it ;-)

Yes, it worked for me,...

It depends on your test case, but in many situations the planner will
flatten that into the same result as the other. The basic problem is
that "(foo).*" is expanded to "(foo).f1, (foo).f2, ..." which is OK if
foo is just a variable referring to a subquery output --- but if the
subquery gets flattened into the parent then your function appears
textually multiple times in the resulting query.

There's been some discussion of disabling flattening when the subquery
output targetlist contains any volatile functions, but that seems like
rather a performance-losing answer. It doesn't completely address the
complaint anyway since even a non-volatile function might be expensive
to compute.

regards, tom lane

Michael Fuhr <mike@fuhr.org> writes:

Maybe one of the developers can comment on why your example calls
the function only once and mine calls it multiple times per row,
even though they look similar.

Look at the EXPLAIN results --- one case gets flattened into a single
plan node and the other doesn't. I know exactly where that particular
skeleton is buried, too:

/*
* Hack: don't try to pull up a subquery with an empty jointree.
* query_planner() will correctly generate a Result plan for a
* jointree that's totally empty, but I don't think the right things
* happen if an empty FromExpr appears lower down in a jointree. Not
* worth working hard on this, just to collapse SubqueryScan/Result
* into Result...
*/
if (subquery->jointree->fromlist == NIL)
return false;

regards, tom lane