To Convert From Python Arrays To Postgresql Quickly?

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 q

Solution 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.

CREATEOR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGINEXECUTE format($$
      DROPTRIGGER IF EXISTS insaft_%1$s_ids ON%1$s;
      CREATETRIGGER insaft_%1$s_ids
      AFTER INSERTON%1$s
      FOREACHROWEXECUTEPROCEDURE 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:

DROPTRIGGER 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.