How to fix my postgres function syntax to fit a user parameter

Question

I'm trying to make a postgreql function, I'm a little confused on which syntax format to use when I'm passing a select statement that takes in a parameter (this parameter can be a string or a list of strings)

Here is the original select query that I used before, with placeholders as my parameter. This select works great for me!

SELECT DISTINCT ON (symbol) symbol,
                next_dividend_date, ex_dividend_date
           FROM api.security_stats 
           WHERE api.security_stats.symbol 
           IN ({placeholders}) 
           ORDER BY symbol, date desc;
   

The problem is when I tried and transform the above into a usable function, where users can enter the parameter for the function. I can't get the syntax to work however. Also, there are a lot of syntax examples in the psql documentation for functions. I'm not sure which one to use for my case?

CREATE FUNCTION dividend(stocks char)
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 
                                                IN ({stocks}) ORDER BY symbol, date desc) 
                                                $$ LANGUAGE SQL;

I'm trying to allow for the parameter, stocks, to be inputted by the user client.

Thank you for any help!

Answer 1

I think you want something like:

create function dividend(p_stocks char)
    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(string_to_array($1, ','))
    order by symbol, date desc 
$$ language sql;

That's table-returning SQL function; you can refer to the input argument with positional notation $1.

Between the lines of your question, I understand that the argument is a comma-separated string, so I added an extra step to parse it to an array, that can then be searched using operator any.

Or, if you want to pass your input as an array:

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;