Store Select Query's Output in One Array in Postgres

Store multiple columns result query in array variable

I solved the issue by using

    select array(select row(bast_id,bast_valuedate,bast_lasttransmission)
    into statements_part_array
    from bast_bankstatement
    inner join baac_bankaccount
    on bast_baac_id = baac_bankaccount.baac_id 
    where baac_folder_id = folderid
    and (bast_valuedate between date1 and date2)
    and bast_state = 1) 
    as statements_part_array;   

PL/pgSQL SELECT into an array

Faster and simpler with a FROM clause in your UPDATE statement:

UPDATE team_prsnl p
SET    updt_dt_tm = now()
     , last_access_dt_tm = now()
FROM   tmp_team_list t
WHERE  p.team_id = t.team_id;

That aside, while operating with an array, the WHERE clause would have to be:

WHERE p.team_id = ANY (team_ids)

The IN construct works with lists or sets, not with arrays. See:

• How to use ANY instead of IN in a WHERE clause with Rails?

in postgres select, return a column subquery as an array?

Use the aggregate function:

select
    usr_id, 
    name, 
    array_agg(tag_id) as tag_arr
from users
join tags using(usr_id)
group by usr_id, name

or an array constructor from the results of a subquery:

select
    u.usr_id, 
    name, 
    array(
        select tag_id 
        from tags t 
        where t.usr_id = u.usr_id
        ) as tag_arr
from users u

The second option is a simple one-source query while the first one is more generic, especially convenient when you need more than one aggregate from a related table. Also, the first variant should be faster on larger tables.

Note, that for better performance the usr_id columns in both tables should be indexed. While typically users.usr_id is a primary key, sometimes one may forget that the index of referencing column is also useful.

How to `read` postgresql `select` output to bash array,one match is one element?

This is going wrong because of the read res <<< $res_temp. What do you expect to retrieve from that?

I've fixed your script and put an example how to directly create an array (which I think you're trying). I don't have Postgresql running atm, but SQLite does the same.

How I created the data:

$ sqlite ./a.test
sqlite> create table test(col1 int, col2 varchar(100),col3 varchar(100));
sqlite>  insert into test values (1,'abc','2015-09-10');
sqlite>  insert into test values (1,'abd2','2015-09-11');
sqlite>  insert into test values (21,'xaz','2015-09-12');
sqlite>  insert into test values (2,'xyz','2015-09-13');
sqlite>  insert into test values (3,'tcs','2015-01-15');
sqlite>  insert into test values (3,'tcs','2016-01-18');
sqlite> SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';

Your solution

#! /bin/bash
res_tmp=$(sqlite ./a.test "SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';")
read -a res <<< ${res_tmp[@]}

echo ${#res[@]}
for i in "${!res[@]}"; do
  printf "%s\t%s\n" "$i" "${res[$i]}"
done

exit 0

Directly an array

#! /bin/bash
res=($(sqlite ./a.test "SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';"))

echo ${#res[@]}
for i in "${!res[@]}"; do
        printf "%s\t%s\n" "$i" "${res[$i]}"
done

exit 0

Oh, and output of both:

3
0       1|abc
1       1|abd2
2       21|xaz

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

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.

---

Related Topics