SQL select statement for every element in an array

I have a SQL table on a server with two columns, short_names and long_names, like this:

Short | Long
________________
Max   | Maximilian
Tom   | Thomas

I am given an arbitrary array of short names and need to produce an array of their matching long names. I know how to do this with multiple select statements, but was thinking it would be better to input a list of the given short names and receive a list back so that I can minimise the requests sent to the server. Aside from basic joins I'm not too great with SQL, so was hoping someone might be able to give me some pointers. The language can be either plpgsql or standard sql.

I suppose the code could be something along the lines of:

CREATE FUNCTION get_long_names(short_names TEXT[])
RETURNS TEXT[] AS
$$
BEGIN
    -- For every element in short_names
    -- SELECT long FROM mytable WHERE short == element
    -- Put whatever is selected in array that is being returned
END
$$
LANGUAGE plpgsql;

But I'm not sure what to put in the middle! Would really appreciate any help.



Read more here: https://stackoverflow.com/questions/65722868/sql-select-statement-for-every-element-in-an-array

Content Attribution

This content was originally published by Tom F at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: