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

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

Tobia Published in 2017-12-07 16:19:59Z

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)',
    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 ...',
    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?

Jasen Reply to 2017-12-08 04:13:06Z

I'm using code very like this in production:

EXECUTE ( SELECT 'insert into sometable ' 
    '('||string_agg(quote_ident(p.key),',') ||
            ') values ('|| string_agg(quote_nullable(p.value),',') || ');'
            FROM each(payload) as p

but here payload is a hstore, not a pair of arrays.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO