Using execute to store column data in an array in postgresql

Question

I am creating a dynamic query to extract some data from a table. I want to store that data in a variable but it cannot store more that one value. And I can also use temp table but in some cases I am not allowed to use that also. Now I was trying to store data in array form:

Example: say df_id='select col from schema.table_name' is the dynamic query created during run time.

declare 
var_tmp varchar [];
BEGIN
    execute 'select array_agg(col) 
          into var_tmp  from ('||df_id||') as y';

but I am getting

error: no schema has been selected.

How Can I perform this? Also, Is there any other alternative to store multiple data in a function?

score 0 · Answer 1 · answered May 05 '18 at 05:50

In general your idea does work. But you have to move into var_tmp out of the query string and make it part of EXECUTE.

Example:

CREATE TABLE a (a varchar(8));
INSERT INTO a VALUES ('a'), ('b'), ('c');

DO
$$
declare 
var_tmp varchar [];
col varchar = 'a';
BEGIN
    execute 'select array_agg('||col||') 
            from a' into var_tmp; 
  raise notice '%', array_to_string(var_tmp, ', ', '<NULL>');
END
$$ language plpgsql

Prints:

NOTICE:  a, b, c
CONTEXT:  PL/pgSQL function inline_code_block line 8 at RAISE

The "error: no schema has been selected." seems to be an other issue not related to your code.

Using execute to store column data in an array in postgresql

1 Answers1