Re: extra function calls from query returning composite type

Ronald Peterson wrote
> I added a 'raise notice' to a plpgsql function I was working on
> recently, and noticed that my notification was being raised more often
> than I expected. The notification is raised in a function ('getone'
> in my example below) that returns a single composite value. This
> function is then called by another function ('getset') that returns a
> setof that composite value. It appears that 'getone' is called once
> for each column of my composite type. I whittled this down to the
> following example.
>
> I get the expected result from my query, but I don't understand (what
> appear to be) the extra function calls.

Working as intended. It is an implementation artifact. You have two
options if you are using the latest couple of releases.

Use LATERAL
Use CTE/WITH

WITH funceval AS (
SELECT func_call(...)
)
SELECT (func_call).* FROM funceval

In the CTE version you cause the function to fully resolve without referring
to any of its component columns and then, in the outer query, explode the
result of the composite type.

The LATERAL syntax is documented but basically (not tested or personally
have I had a chance to use the feature myself)...

SELECT *
FROM dat LATERAL getone(dat.id)
;

David J.

--
View this message in context: http://postgresql.nabble.com/extra-function-calls-from-query-returning-composite-type-tp5832275p5832282.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.