Thread: Passing dynamic parameters to a table-returning function

Hi all,

I know I'm probably missing something obvious here, but I have been unable
to figure this out or find any docs on it.

I have a function that takes in a postal address and normalizes it through
text manipulation etc.:

*fn_normalize_address*(*in_line_one* character varying, *in_line_two *character
varying, *in_line_three* character varying, *in_city* character varying, *
in_locality* character varying, *in_region* character varying, *
in_postal_code* character varying, *in_country* character varying)
*RETURNS TABLE*(*line_one* character varying, *line_two* character varying,
*line_three* character varying, *city* character varying,
*locality*character varying,
*region* integer, *region_string* character varying,
*postal_code*character varying,
*country* integer, *country_string* character varying)


I also have a table of addresses that I'd like to normalize:

                                      Table "public.tb_address"
        Column        |          Type          |
 Modifiers
----------------------+------------------------+-----------------------------------------------------
 address              | integer                | not null default
nextval('sq_pk_address'::regclass)
 name                 | character varying(64)  |
 line_one             | character varying(256) | not null
 line_two             | character varying(256) |
 line_three           | character varying(256) |
 city                 | character varying(256) | not null
 locality             | character varying(256) |
 region               | integer                | not null
 zip_postal           | character varying(32)  | not null
 zip_postal_extension | character varying(32)  |



I am trying to write a query to update the addresses in this table with
their normalized versions. Each address is uniquely identified by the
integer surrogate primary key "tb_address.address".

I'm having trouble because I can't figure out how to select the primary key
from that table, as well as all the columns from the function's output. 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 *.

Any help would be appreciated.

Thanks,
Moshe


--
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>

On Tuesday, February 05, 2013 2:22 AM Moshe Jacobson wrote:
Hi all,


> I'm having trouble because I can't figure out how to select the primary
key from that table, as well as all the columns > from the function's
output. 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 *.

Can you try with RETURN NEXT syntax. For example, refer below link:
http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PL
PGSQL-STATEMENTS-RETURNING

With Regards,
Amit Kapila.

On Tue, Feb 5, 2013 at 9:09 AM, Amit Kapila <amit.kapila@huawei.com> wrote:

>
> Can you try with RETURN NEXT syntax. For example, refer below link:
>
> http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PL
> PGSQL-STATEMENTS-RETURNING
>

Thanks for your response, but I'm looking for simple SQL syntax that can be
executed outside of a stored procedure.

Moshe

--
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>

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.

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>