Re: Passing dynamic parameters to a table-returning function

Perfect, that is exactly what I needed. Thanks David!


On Mon, Feb 4, 2013 at 6:49 PM, David Johnston <polobo@yahoo.com> wrote:

> Moshe Jacobson wrote
> >  I'm
> > unsure of the syntax for passing in values from columns in the database
> as
> > the parameters of a set-returning function from which I want to select *.
>
> General form for this in version <= 9.2:
>
> WITH func_call_cte AS (
> SELECT func_call(col1, col2, col3) AS func_result
> FROM table_with_cols_123
> )
> SELECT (func_result).*
> FROM func_call_cte;
>
> 9.3 will have "LATERAL" and so should be much cleaner.
>
> The general idea is you have to put the function into the select-list so it
> can see the columns of the table in the FROM.  To avoid multiple
> evaluations
> of the function you have to treat the output set as a single typed column
> (func_result) and then in an outer query layer (in this case outside the
> WITH) you can generically expand the typed column into its component parts.
>
> HTH,
>
> David J.
>
>
>
> --
> View this message in context:
>
http://postgresql.1045698.n5.nabble.com/Passing-dynamic-parameters-to-a-table-returning-function-tp5743680p5743726.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

"People don't multitask because they're good at it. They do it because they
are more distracted" -- David
Sanbonmatsu<http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0054402>