postgres function: pass and iterate through array

Question

I have no clue if what I'm trying to do is possible in PostgreSQL. But here we go: I want to pass an array of strings to a function. Then I want to iterate through this array and want to SELECT data from a table based on the current string.

The selected data should be pushed to a new array(or table?) which is returned when the iteration is finished.

Here is a simplified version of what I'm trying to do in JavaScript:

const symbols = ["PAM", "EVC", "BIBI", "AA"];

const getLatestQuotes = (tickerSymbols) => {
  const dataToReturn = [];
  tickerSymbols.forEach((symbol) => {
    // do something with symbol
    const newVal = "bla bla" + symbol;

    dataToReturn.push(newVal);
  });

  return dataToReturn;
};

const quotes = getLatestQuotes(symbols);

console.log("quotes", quotes);

EDIT: here is what I have SQL-wise so far:

CREATE OR REPLACE FUNCTION get_latest_quotes
(
    ArrayText character varying[]
)
RETURNS TABLE (ticker VARCHAR(10), quotetime text, price text) AS
$BODY$
DECLARE
    Counter INT = 0 ;
BEGIN
    CREATE TEMP TABLE ArrayData(ticker VARCHAR(10), quotetime text, price text);
    FOR Counter in array_lower(ArrayText, 1) .. array_upper(ArrayText, 1)
    LOOP
        INSERT INTO ArrayData VALUES(
      SELECT ticker, quotetime, price FROM quotes WHERE ticker = ArrayText[Counter];
    );
    END LOOP;

    RETURN QUERY
    SELECT ArrayTextData
    FROM ArrayData;

    DISCARD TEMP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I hope this kind of explains what I'm trying to do.

EDIT2: Thank you @a_horse_with_no_name, that helps a lot. One more question though. What I get back looks like this:

{ ticker: 'AAPL', timestamp: '934934400', price: 0.54000002145767 },
{ ticker: 'AAPL', timestamp: '935020800', price: 0.51999998092651 },
{ ticker: 'AAPL', timestamp: '935107200', price: 0.52999997138977 },
{ ticker: 'AAPL', timestamp: '935366400', price: 0.54000002145767 },
{ ticker: 'AAPL', timestamp: '935452800', price: 0.54000002145767 },
{ ticker: 'AAPL', timestamp: '935539200', price: 0.55000001192093 },
.
.
.
{ ticker: 'MSFT', timestamp: '934848000', price: 0.54000002145767 },
{ ticker: 'MSFT', timestamp: '934934400', price: 0.54000002145767 },
{ ticker: 'MSFT', timestamp: '935020800', price: 0.51999998092651 },
{ ticker: 'MSFT', timestamp: '935107200', price: 0.52999997138977 },
{ ticker: 'MSFT', timestamp: '935366400', price: 0.54000002145767 },
{ ticker: 'MSFT', timestamp: '935452800', price: 0.54000002145767 },
{ ticker: 'MSFT', timestamp: '935539200', price: 0.55000001192093 },
.
.
.
{ ticker: 'GOOGL', timestamp: '934848000', price: 0.54000002145767 },
{ ticker: 'GOOGL', timestamp: '934934400', price: 0.54000002145767 },
{ ticker: 'GOOGL', timestamp: '935020800', price: 0.51999998092651 },
{ ticker: 'GOOGL', timestamp: '935107200', price: 0.52999997138977 },
{ ticker: 'GOOGL', timestamp: '935366400', price: 0.54000002145767 },
{ ticker: 'GOOGL', timestamp: '935452800', price: 0.54000002145767 },
{ ticker: 'GOOGL', timestamp: '935539200', price: 0.55000001192093 },

Now, I only want the row for each ticker symbol with the newest timestamp. How can I do this with SQL?

EDIT3:

I've managed to figure it out (almost):

CREATE OR REPLACE FUNCTION get_latest_quotes(ArrayText character varying[])
  RETURNS TABLE (ticker VARCHAR(10), "timestamp" int8, price float8) AS
$BODY$
  SELECT ticker, timestamp, price 
  FROM base.quotes 
  WHERE ticker = any(arraytext) 
  ORDER BY timestamp DESC
  LIMIT 10;
$BODY$
LANGUAGE sql
stable;

That does almost what I want. This is what it gives me:

AAPL    1615901400  123.99
MSFT    1615901400  234.81
RIO.AX  1615852800  114.13
AAPL    1615838402  123.99

As you see, FB is missing, because it has an older timestamp than AAPL. How can can I tell SQL that I only want one row for each ticker symbol I pass?

user330315 · Accepted Answer · 2021-03-14 19:57:01Z

2

You don't need a loop, a temp table or even PL/pgSQL for this.

This can be achieved with a single query:

CREATE OR REPLACE FUNCTION get_latest_quotes(ArrayText character varying[])
  RETURNS TABLE (ticker VARCHAR(10), quotetime text, price text) AS
$BODY$
  SELECT ticker, quotetime, price 
  FROM quotes 
  WHERE ticker = any(arraytext);
$BODY$
LANGUAGE sql
stable;

You can use the function like a table:

select *
from get_latest_quotes(array['PAM', 'EVC', 'BIBI', 'AA']);

answered Mar 14, 2021 at 19:57

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Ic3m4n Over a year ago

Thank you so much. I have one follow-up question. I've edited my post. Would be great if you could take another look

Collectives™ on Stack Overflow

postgres function: pass and iterate through array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related