0

I'm using postgrest as an API wrapper to my postgres db. The api reads my database schema and creates URL using common characters in the address.

I have a function like so = create function api.dividend(p_stocks text[])

A general API call:

/url?key=val;key2=val2

My API call for function create function api.dividend(p_stocks text[]):

/dividend?p_stocks=AAPL

In this case to test things out, I used AAPL as the input parameter. However this is the error I'm getting:

function api.dividend(p_stock => text) does not exist

As you see the return error is expecting this function, api.dividend(p_stock => text) However I tried creating a function using those parameters and I dont think its possible...

I would greatly appreciate some advice or suggestions on what suggestions I should try?

This isn't a schema problem because I've checked if the function is in the appropriate schema.

Here is the full function from postgres, if this helps:

create function dividend(p_stocks text[])
    returns table(symbol char, next_dividend_date date, ex_dividend_date date) 
as $$
    select distinct on (symbol) symbol, next_dividend_date, ex_dividend_date
    from api.security_stats 
    where api.security_stats.symbol = any($1)
    order by symbol, date desc 
$$ language sql;
andres
  • 1,558
  • 7
  • 24
  • 62
  • 1
    Well you declared `p_stocks` as a text array. Pretty sure the error is coming from just passing a text string to it instead of a text array. Postgres allows function overloading so it is looking for `create function dividend(p_stocks text)` and not finding it and hence the error. – Adrian Klaver Dec 07 '20 at 19:49
  • I understand the error now, I've tried passing it like so previously `/dividend?p_stock=[AAPL]` and still not getting it to work. Is that how you assumed the request would look like? – andres Dec 07 '20 at 19:57
  • 1
    No, that would end up as `create function dividend('[AAPL]')` and that is not how you input an array. That is done as either `'{AAPL}` or `ARRAY['AAPL']`. At some point in the process you have to take the parameters passed in and convert them to one of the forms above before using in the function. I don't use `postgrest`, but I did find this [Using array](https://postgrest.org/en/v7.0.0/api.html?highlight=array#calling-functions-with-array-parameters) – Adrian Klaver Dec 07 '20 at 21:06
  • Oh wow I was so laser focused on the previous doc segments I completely missed out on the array parameter! Makes total sense, thank you so much for your help!! – andres Dec 07 '20 at 21:44

1 Answers1

1

I managed to solve the issue with the help of @Adrian,

/dividend?p_stocks=%7BAAPL%7D

This would be the correct URL path for postgrest to use the function/parameter. %7b = { , and %7D = }

Now the URL has the correct array format for my function.

andres
  • 1,558
  • 7
  • 24
  • 62