calling a function with parameters in insert

Question

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?

• András Váczi: Re2: Finding out what the DB does with UNIONs is the easiest by running the query with EXPLAIN ANALYZE. You will see some Appends there, which means that each part of the UNION gets handled basically separated from the others. Re3: as always, it depends... Re4: and what to do if you skip the checks which later turn out to be violated?

Answer 1

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.

• arthur: this is clearly a very ellegant solution

Answer 2

Aparently I was able to invoke such a command without any problems:

insert into wgs84 (wkb_geometry,wkb_geography) values
(st_transform(st_setsrid(st_makepoint( 694274.6998, 5341745.2959), 25832),4326),
st_transform(st_setsrid(st_makepoint( 694274.6998, 5341745.2959), 25832),4326)::geography)

which shows that a plain insert is possible.. I wonder why postgres then was once confused about too many commas..