Re: PL/pgSQL: How to return two columns and multiple rows - Mailing list pgsql-general

From David G. Johnston
Subject Re: PL/pgSQL: How to return two columns and multiple rows
Date
Msg-id CAKFQuwZbxsifWoG_ft=EzjnQaPS1DBiEN7X52152SLwoX-CY2Q@mail.gmail.com
Whole thread Raw
In response to Re: PL/pgSQL: How to return two columns and multiple rows  (Sven Geggus <lists@fuchsschwanzdomain.de>)
List pgsql-general
On Thu, Jun 18, 2015 at 9:52 AM, Sven Geggus <lists@fuchsschwanzdomain.de> wrote:
Raymond O'Donnell <rod@iol.ie> wrote:

>> mydb=> select myfunc('foo','bar');
>
> You need to do:
>
>    select * from myfunc('foo','bar');

This has been a misguided example. Reality should more likely look like this:

select myfunc(col1,col2) from mytable;

And it would of course be undesired if myfunc would be called twice per row.
So how would this look like to avoid the function beeing called twice?

​WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable )
SELECT (exec_func.myfunc).* FROM exec_func;

This relies on the fact that currently a CTE introduces an optimization barrier.

David J.
 

pgsql-general by date:

Previous
From: Sven Geggus
Date:
Subject: Re: PL/pgSQL: How to return two columns and multiple rows
Next
From: Tom Lane
Date:
Subject: Re: writable cte triggers reverse order