CODEWITHSUNDEEP
arrayspostgresqlfunctionvariablesplpgsql
h3llca7
h3llca7

Reputation: 61

postgresql array_length to int variable

I have an array of strings 

sarr := array['s', 's1', ..., 'sn'];
a integer;

I need to put array_length(sarr, 1) to a integer variable:

a := select(array_length(sarr, 1)) or a := array_length(sarr, 1) doesn't work

a variable used here only

for iter in 1..a
loop
    execute 'alter table temp_table add column col_' || iter || ' 
varchar;';
end loop;

Upvotes: 1

Views: 2233

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

There is some kind of misunderstanding. This just works in modern Postgres:

-- DROP TABLE IF EXISTS pg_temp.temp_table;
CREATE TEMP TABLE temp_table (id int PRIMARY KEY);

DO
$do$
DECLARE
   sarr text[]  := array['s', 's1', 'sn'];
   a    integer;
   iter integer;
BEGIN
   a := array_length(sarr, 1);

   FOR iter IN 1..a
   LOOP
      RAISE NOTICE '%', -- safety; replace with execute after testing
   -- EXECUTE 
         'ALTER TABLE temp_table ADD COLUMN col_' || iter || ' varchar;';
   END LOOP;
END
$do$

But it's inefficient. Execute a single ALTER command adding multiple columns instead. No loop:

DO
$do$
DECLARE
   sarr text[] := array['s', 's1', 'sn'];
BEGIN
   EXECUTE (
      SELECT 'ALTER TABLE temp_table2 ADD COLUMN col_'
          || string_agg(i::text || ' varchar', ', ADD COLUMN col_')
      FROM   generate_subscripts(sarr, 1) i
      );
END
$do$;

Related:

Upvotes: 1

Related Questions