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;
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:
a := array_length(sarr, 1)should work just fine. What is the error you get?copycommand, not a PL/pgSQL compiler error