To convert from Python arrays to PostgreSQL quickly?

Question

This is a follow-up question to: How to cast to int array in PostgreSQL?
I am thinking how to convert Python's datatype of array-array of signed integer into to int of PostgreSQL quickly:

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

where [] should be replaced by {} and surrounded by ' if manually. In PostgreSQL, the following is accepted as the syntax of the datatype

...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{1,2},{3,4}}'); 

@JohnMee's suggestion

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

@ErwinBrandstetter's suggestion

Stick to signed integers because it is supported by SQL standard. Map to int, so just in PostgreSQL side:

TG_ARGV::int[]

I want to stick to this Erwin's suggestion.

Test run of simpler version of @ErwinBrandstetter's answer

I have to simplify his answer to keep it enough focused here by removing the table-name from function so just keeping the trigger for one initial table measurements:

CREATE OR REPLACE FUNCTION f_create_my_trigger(_arg0 text)
  RETURNS void AS
$func$
BEGIN
EXECUTE format($$
    DROP TRIGGER IF EXISTS insaft_ids ON measurements;
    CREATE TRIGGER insaft_ids
    AFTER INSERT ON measurements
    FOR EACH ROW EXECUTE PROCEDURE insaft_function(%1$L)$$
    , _arg0
);

END
$func$ LANGUAGE plpgsql;

And I run:

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

But get empty output:

 f_create_my_trigger 
---------------------

(1 row)

How can you map to int for PostgreSQL 9.4 in Python?

Answer 1

Setup

You want to create triggers (repeatedly?) using the same trigger function like outlined in my related answer on dba.SE. You need to pass values to the trigger function to create multiple rows with multiple column values, hence the two-dimensional array. (But we can work with any clearly defined string!)

The only way to pass values to a PL/pgSQL trigger function (other than column values of the triggering row) are text parameters, which are accessible inside the function as 0-based array of text in the special array variable TG_ARGV[]. You can pass a variable number of parameters, but we discussed a single string literal representing your 2-dimenstional array earlier.

Input comes from a 2-dimensional Python array with signed integer numbers, that fits into the Postgres type integer. Use the Postgres type bigint to cover unsigned integer numbers, as commented.

The text representation in Python looks like this:

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

Syntax for a Postgres array literal:

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

And you want to automate the process.

Full automation

You can concatenate the string for the CREATE TRIGGER statement in your client or you can persist the logic in a server-side function and just pass parameters.

Demonstrating an example function taking a table name and the string that's passed to the trigger function. The trigger function insaft_function() is defined in your previous question on dba.SE.

CREATE OR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void
  LANGUAGE plpgsql AS
$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
      FOR EACH ROW EXECUTE PROCEDURE insaft_function(%2$L)$$
                , _tbl
                , translate(_arg0, '[]', '{}')
      );
END 
$func$;

Call:

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

Or:

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

db<>fiddle here
_{Old sqlfiddle}

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

This function drops a trigger of the same name if it exists, before creating the new one. You may want to drop or keep this line:

DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;

This runs with the privileges of the calling DB role. You could make it run with superuser (or any other) privileges if need be. See:

• Is there a way to disable updates/deletes but still allow triggers to perform them?

There are many ways to achieve this. It depends on exact requirements.

Explaining format()

format() and the data type regclass help to safely concatenate the DDL command and make SQL injection impossible. See:

• Table name as a PostgreSQL function parameter

The first argument is the "format string", followed by arguments to be embedded in the string. I use dollar-quoting, which is not strictly necessary for the example, but generally a good idea for concatenating long strings containing single-quotes: $$DROP TRIGGER ... $$

format() is modeled along the C function sprintf. %1$s is a format specifier of the format() function. It means that the first (1$) argument after the format string is inserted as unquoted string (%s), hence: %1$s. The first argument to format is _tbl in the example - the regclass parameter is rendered as legal identifier automatically, double-quoted if necessary, so format() does not have to do more. Hence just %s, not %I (identifier). Read the linked answer above for details.
The other format specifier in use is %2$L: Second argument as quoted string literal.

If you are new to format(), play with these simple examples to 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 the manual.