From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: extra function calls from query returning composite type |
Date: | 2014-12-29 15:54:00 |
Message-ID: | 1419868440424-5832282.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-12-29 15:54:49 | Re: extra function calls from query returning composite type |
Previous Message | Adrian Klaver | 2014-12-29 15:49:20 | Re: Rollback on include error in psql |