Postgresql – calling a function with parameters in insert

bulk-insertinsertpostgresql

Is it possible in SQL ellegantly achieve the following:

insert into my_table (the_geom) VALUES 
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    .....

which involves invoking an external function geomfromwkb with two parameters?

So far I have a un-elegant solution. The first solution is of this type:

insert into mytable (the_geom) select 
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    .....

The second one uses the prepare statement:

PREPARE my_insert AS INSERT INTO geom_example (the_geom) 
    VALUES (geomfromwkb($1,4326));
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
statement: DEALLOCATE my_insert;

Question 1: Is there anything more ellegant than this?

Question 2: What does the DB do when it sees a bunch of unions?

Question 3: does the prepare really speed up things compared to one fat insert?

Question 4: the byte sequence is of the geometry type. It is possible to skip the consistency checks (to insure that this is indeed the geometry byte sequence) to speed up inserts?

Best Answer

You can build a 'table' using the VALUES clause and build the function call on top of it:

INSERT INTO wgs84 (wkb_geometry, wkb_geography)
SELECT st_transform(st_setsrid(st_makepoint(col1, col2), col3), col4)
  FROM (
        VALUES (694274.6998, 5341745.2959, 25832, 4326),
               ( 69425.6998, 5341754.23,   25834, 4327)
       ) t (col1, col2, col3, col4);

This is easier to read, to write, and to modify than the repeated function calls. You can even align your data which sometimes makes finding errors easier.

A version of this can be putting the VALUES into a WITH query. In some cases it is better to put (preferably using the COPY command) the data into a temporary table and selecting from there in the INSERT statement - this comes handy when the input size gets bigger.

Related Question