postgresql array_length to int variable

Question

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;

`a := array_length(sarr, 1)` should work just fine. What is the error you get? — a_horse_with_no_name, Oct 26 '17 at 13:39
if I declare a := 10 for example I haven't got any error and my function work normal, bur if I set a := array_length(sarr, 1) I got this: " extra data after last expected column ..." — h3llca7, Oct 26 '17 at 13:42
Show us the **complete** code. "*extra data after last expected column*" is an error from the `copy` command, not a PL/pgSQL compiler error — a_horse_with_no_name, Oct 26 '17 at 13:45

Erwin Brandstetter · Answer 1 · 2017-10-28T19:47:35.787

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$;

postgresql array_length to int variable

1 Answers1