Thread: table value function help
Hello,
I have a table value function and would like the first and second input to take multiple arguments (array or list) I suppose. Like the below:
create or replace function myfunction(sector, agency, term)
returns table (cusip char(9), sector char(12))
language sql
stable
as $function$
select foo
from
atable
where
sector in (myfunction.sector)
usage
sectorselect('sector_1 sector_2', 'agent_1 agent_2, 120)
On 11/22/18 8:25 AM, Glenn Schultz wrote:
> Hello,
>
> I have a table value function and would like the first and second input
> to take multiple arguments (array or list) I suppose. Like the below:
I cannot follow what you are trying to achieve.
>
>
> create or replace function myfunction(sector, agency, term)
> returns table (cusip char(9), sector char(12))
> language sql
> stable
> as $function$
>
> select foo
> from
> atable
> where
> sector in (myfunction.sector)
From above:
1) Where is term used?
2) Where is agency used?
3) If you are returning two fields why is there only one in the query?
It might be best to outline the procedure you want to execute. As example:
1) Consume inputs of this type and layout.
2) Process inputs this way.
3) Output this.
>
> usage
>
> sectorselect('sector_1 sector_2', 'agent_1 agent_2, 120)
I going to assume sectorselect = myfunction, correct?
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thursday, November 22, 2018, Glenn Schultz <glenn@bondlab.io> wrote:
Hello,I have a table value function and would like the first and second input to take multiple arguments (array or list) I suppose. Like the below:create or replace function myfunction(sector, agency, term)
Create function myfunction(arg1 text[], arg2 text[]) ...
David J.
p.s. You should avoid using the “char” data type in PostgreSQL, use text or varcar instead.
Thanks!
I googled this for 3-days before coming here. I see what you mean. Thank you so much will make the recommended changes.
Glenn
On Thu, Nov 22, 2018 at 11:02 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, November 22, 2018, Glenn Schultz <glenn@bondlab.io> wrote:Hello,I have a table value function and would like the first and second input to take multiple arguments (array or list) I suppose. Like the below:create or replace function myfunction(sector, agency, term)Create function myfunction(arg1 text[], arg2 text[]) ...David J.p.s. You should avoid using the “char” data type in PostgreSQL, use text or varcar instead.