To convert from Python arrays to PostgreSQL quickly? -

this follow-up question to: how cast int array in postgresql?
thinking how convert python's datatype of array-array of signed integer int of postgresql quickly:

import numpy np; # use data format of python here event = np.array([[1,2],[3,4]]); 

where [] should replaced {} , surrounded ' if manually. in postgresql, following accepted syntax of datatype

... each row execute procedure insaft_function('{{1,2},{3,4}}');  

@johnmee's suggestion

str(event).replace('[','{').replace(']','}').replace('\n ',',') 

@erwinbrandstetter's suggestion

stick signed integers because supported sql standard. map int, in postgresql side:

tg_argv::int[] 

i want stick erwin's suggestion.

test run of simpler version of @erwinbrandstetter's answer

i have simplify answer keep enough focused here removing table-name function keeping trigger 1 initial table measurements:

create or replace function f_create_my_trigger(_arg0 text)   returns void $func$ begin execute format($$     drop trigger if exists insaft_ids on measurements;     create trigger insaft_ids     after insert on measurements     each row execute procedure insaft_function(%1$l)$$     , _arg0 );  end $func$ language plpgsql; 

and run:

sudo -u postgres psql detector -c "select f_create_my_trigger('[[1,2],[3,4]]');" 

but empty output:

 f_create_my_trigger  ---------------------  (1 row) 

how can map int postgresql 9.4 in python?

summing situation

you want create triggers (repeatedly?) using same trigger function outlined in my previous answer on dba.se. need pass values trigger function create multiple rows multiple column values, hence two-dimensional array. (but can work any defined string!)

the way pass values pl/pgsql trigger function text parameters, accessible inside function 0-based array of text tg_argv[]. pass variable number of parameters, discussed single string literal representing 2-dimenstional array earlier.

the values want pass come 2-dimenstional python array signed integer numbers, fit postgres integer type. have use postgres type bigint unsigned integer numbers sure, like commented.

the text representation in python looks this:

[[1,2],[3,4]] 

the syntax postgres array literal looks this:

{{1,2},{3,4}} 

and seems want automate process.

full automation

you can concatenate string create trigger statement in client or can persist logic in server-side function , pass parameters.

demonstrating example function pass table name , string that's used parameter trigger function. trigger function insaft_function() defined in previous question on dba.se.

create or replace function f_create_my_trigger(_tbl regclass, _arg0 text)   returns void $func$ begin  execute format($$    drop trigger if exists insaft_%1$s_ids on %1$s;    create trigger insaft_%1$s_ids    after insert on %1$s    each row execute procedure insaft_function(%2$l)$$    , _tbl, translate(_arg0, '[]', '{}')    );  end  $func$ language plpgsql; 

call:

select f_create_my_trigger('measurements', '[[1,2],[3,4]]'); 

or:

select f_create_my_trigger('some_other_table', '{{5,6},{7,8}}'); 

sql fiddle demo.

now can pass either [[1,2],[3,4]] (with square brackets) or {{1,2},{3,4}} (with curly braces). both work same. translate(_arg0, '[]', '{}' transforms first second form.

this function automatically drops trigger of same name if exists, before creating new one. may want drop or keep line:

drop trigger if exists insaft_%1$s_ids on %1$s; 

this runs privileges of calling db role. make run superuser (or other) privileges if need be.

there many other ways achieve this. depends on exact requirements.

explaining format()

format() , data type regclass safely concatenate ddl command while making sql injection impossible. details:

• table name postgresql function parameter

the first argument "format string", followed arguments imbedded in string. use dollar-quoting, not strictly necessary example, idea concatenating long strings might contain literal quotes: $$drop trigger ... $$

format() modeled along c function sprintf. %1$s format specifier of format() function. means first (1$) argument after format string inserted unquoted string (%s), hence: %1$s. first argument format _tbl in example - regclass parameter rendered legal identifier automatically, double-quoted if necessary, format() not have more. hence %s, not %i (identifier). read linked answer above details.
other format specifier in use %2$l: second argument quoted string literal.

if new format(), play these simple examples understand:

select format('input -->|%s|<-- here', '[1,2]')      , format('input -->|%s|<-- here', translate('[1,2]', '[]', '{}'))      , format('input -->|%l|<-- here', translate('[1,2]', '[]', '{}'))      , format('input -->|%i|<-- here', translate('[1,2]', '[]', '{}')); 

and read manual.