Home How do I turn an array into a record / tuple / row type?
Reply: 0

How do I turn an array into a record / tuple / row type?

user1264
1#
user1264 Published in July 16, 2018, 8:23 pm

I need to execute a dynamic insert into a table with a variable number of columns.

Right now I'm quoting both the column names, with quote_ident, and the actual values, with quote_nullable, and then joining them with array_to_string:

for ... loop
    ...
    cols := array_append(cols, quote_ident(column_name));
    vals := array_append(vals, quote_nullable(column_value));
end loop;

execute format('insert into %s (%s) values (%s)',
    target_table,
    array_to_string(cols, ', ')
    array_to_string(vals, ', ')
);

It's a pattern found all over the place, including on the official documentation. But it feels a bit unclean. I'd rather pass the array of values a parameter in the using clause:

execute format('insert into %s (%s) ... $1 ...',
    target_table,
    array_to_string(cols, ', ')
) 
using vals;

Notice the using vals, which is what I'd like to achieve. But I cannot seem to be able to fill in the dots in the insert statement. Maybe some kind of select ... from ...?

More generally, how do I turn an array into a record / tuple / row type?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.4074 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO