Search Postgresql Archives

Passing dynamic parameters to a table-returning function

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

 



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@xxxxxxxxxxxx | 
www.neadwerx.com

"People don't multitask because they're good at it. They do it because they are more distracted" -- David Sanbonmatsu

[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