Dynamic Sql: Create array from records using array of column names

Question

I am pulling all of the column_names (cname1) from a crosstab table that I made. There are thousands of these column names so I combined them into an array. I then want to use dynamic sql (or whatever works) to use those column_names to make an array based off of the records of that same crosstab table. I keep getting the error:

ERROR: missing "LOOP" at end of SQL expression

.

CREATE OR REPLACE FUNCTION mffcu.test_ty_hey()
 RETURNS setof record
 LANGUAGE plpgsql
AS $function$
Declare
       cname1 text;         
Begin 

for cname1 in select array_agg(column_name) as useme
from(
select column_name::text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'crosstab_183' 
and ordinal_position != 1
) as fin
join mffcu.crosstab_183 a on fin.id = a.id;

loop  
sql2 ='select distinct array['|| columnname ||'] from mffcu.crosstab_183';
execute sql2;
end loop;
END;
$function$

I cannot for the life of me figure out why I'm getting this error.

Pavel Stehule · Accepted Answer · 2013-05-21 15:59:57Z

1

for cname1 in select array_agg(column_name) as useme
from(
select column_name::text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'crosstab_183' 
and ordinal_position != 1
) as fin
join mffcu.crosstab_183 a on fin.id = a.id; --here should not be semicolon!
loop

answered May 21, 2013 at 15:59

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Sql: Create array from records using array of column names

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related