Search Postgresql Archives

Re: extra function calls from query returning composite type

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks much.  Didn't know about LATERAL.  That's a solution.  Seems like the implementation could be improved though.  The existence of LATERAL seems to imply that it's possible.  Why introduce more complicated syntax?  Of course the syntax applies to more situations than this one.  But this case seems like it could be improved.
I hate complaining.  Especially about my favorite database.  But when a composite type has many columns, this inefficiency really adds up.  And it's pretty invisible, unless you really look into it.
It's on my list of things to do to buy Tom Lane a beer.  It should, in my opinion, be on everyone's list of things to do who is on this list.  This problem has nothing to do with it.  I'm hoping that, altogether, we buy Tom enough beer that that he considers making this query more efficient.  This might involve impairing his better judgement, but I'm willing to drive to the country of Pennsylvania or wherever it is Tom hangs his hat these days to to buy a beer in the cause of improving this query.  Maybe two beers.  I hope you will all chip in a few beers yourselves, and maybe we can fix this esoteric problem that probably only concerns me.

On Mon, Dec 29, 2014 at 10:54 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ronald Peterson <ron@xxxxxxxxxxxxxxxxxx> writes:
> 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.

This:

>     SELECT (getone(id)).*

is implemented as SELECT (getone(id)).foo, (getone(id)).bar

If you're using 9.3 or later you could avoid that by recasting the
call as LATERAL, ie

  SELECT go.*
    FROM dat, LATERAL getone(id) AS go
    WHERE set = setid;

                        regards, tom lane



--
--
Ron Peterson




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux