SQL select statement for every element in an array

Question

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.

score 1 · Accepted Answer · 2021-01-14 17:03:11Z

1

Use =any(), but I would make it a set-returning function (that can be used like a table). And a language sql function is enough, not need for PL/pgSQL

CREATE FUNCTION get_long_names(short_names TEXT[])
  RETURNS table(long_name text, short_name)
as
$$
  SELECT long, short
  FROM mytable 
  WHERE short = any(short_names);
$$
LANGUAGE sql;

If you really want to return a huge array, you can use:

CREATE FUNCTION get_long_names(short_names TEXT[])
  RETURNS text[]
as
$$
  select array(SELECT long
               FROM mytable 
               WHERE short = any(short_names));
$$
LANGUAGE sql;

edited Jan 14, 2021 at 17:03

answered Jan 14, 2021 at 16:37

user330315

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

1 Comment

Tom F Over a year ago

That looks like it will do the trick! Thank you so much

Bjarni Ragnarsson · Accepted Answer · 2021-01-14 16:43:00Z

1

Not tested, but something like this should work:

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
    return(select array_agg(long order by o) 
    from unnest(short_names) with ordinality t(x,o) 
    left join mytable on short=x);    
END
$$
LANGUAGE plpgsql;

answered Jan 14, 2021 at 16:43

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

2 Comments

Laurenz Albe Over a year ago

I like this answer better, because it allows you to figure out which long name belongs to which short name.

Bjarni Ragnarsson Over a year ago

Actually the array here is returned in the same order as the insent array so you have the link.

Collectives™ on Stack Overflow

SQL select statement for every element in an array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related