store all the values of a column returned by a select query in a variable - PostgreSQL

Question

I have a table which maintains records of the transactions carried out within the enterprise.

Now I am writing a function that returns the transaction IDs that were found in the table. Following is the code of the function :

CREATE OR REPLACE FUNCTION searchList(inputList varchar) RETURNS character varying AS $BODY$

DECLARE
newList varchar;
rowcount integer;
splitted varchar[];

BEGIN
splitted = regexp_split_to_array(inputList,','); --splits the string by comma as a delimiter and produces an array as a result

select incidents.transaction_id into newList from incidents where transaction_id IN(select unnest(splitted)); --unnest function expands the array and is replaced by all the values of array

GET DIAGNOSTICS rowcount = ROW_COUNT; -- ROW_COUNT is the literal which is gonna provide the number of rows returned by previous query

IF rowcount = 0 THEN
return 'Match does not exist';
ELSE
return newList;
END IF;
END;
$BODY$ LANGUAGE plpgsql;

I am providing a string as an input to the function and then splits that string to form an array and then I am running a select query that is intended to store the transaction id of all such records into a variable whose transaction id was present within the array and finally return the variable

However, when I execute this function I only get one of the transaction_id as output even if all the transaction_id provided are a match.

I am using select into to store the result into variable however I have a feeling it only stores one of the extreme values of the column(first/last)

Is there any way we can store the result of a single column which may contain multiple entries into the variable.

I know there are multiple threads already here related to select into, however none of them helped. I have recently begin using pgsql so have little knowledge of the subject

@a_horse_with_no_name my bad it is 9.4, even that is ancient but this what came in bundled with the application we are using — Karan Parikh, Dec 31 '19 at 09:16

score 1 · Accepted Answer · 2019-12-31T10:21:09.233

If you want to return all values returned, you need to aggregate somehow. As you seem to want a comma separated list, use string_agg(). There is no need to unnest the array though:

CREATE OR REPLACE FUNCTION searchlist(inputlist text) 
  RETURNS text AS 
$BODY$
DECLARE
  newlist text;
BEGIN
  select string_agg(transaction_id,',')
     into newlist 
  from incidents 
  where transaction_id = any (string_to_array(inputlist, ','));

  IF coalesce(newlist, '') = '' THEN
    return 'Match does not exist';
  ELSE
    return newList;
  END IF;
END;
$BODY$ 
LANGUAGE plpgsql;

I would highly recommend to not pass comma separated values around. Postgres properly supports arrays and I would use an array as a parameter and and array as the return value as well. You also don't really need PL/pgSQL for this.

A cleaner version (in my opinion) would be:

CREATE OR REPLACE FUNCTION search_list(p_inputlist int[]) 
  RETURNS int[]
AS 
$BODY$
  select array_agg(transaction_id)
  from incidents 
  where transaction_id = any (p_inputlist);
$BODY$ 
LANGUAGE sql;

The only difference is that this doesn't return a message indicating that the returned array was empty. If you do that, you can use cardinality() in the code that uses the function to check if no rows were returned.

Thanks a lot! The script that you provided works :) I would also go through cleaner version and have a look. Regarding that LANGUAGE as sql I used to think since I am executing in postgres I need to have it set as plpgsql, thanks for providing new info. Just wanted to understand what difference does it have? Does writing SQL as Language if all the logic uses universal sql commands gives a better speed/efficiency? — Karan Parikh, Jan 02 '20 at 04:33

store all the values of a column returned by a select query in a variable - PostgreSQL

1 Answers1