I don't think you need the FROM Point portion of your statement. Point is a table, so I am guessing that you are inserting the NEW value into the point2 table for every row in the point table.
I would try:
CREATE OR REPLACE FUNCTION function_prueba() RETURNS TRIGGER AS $$
DECLARE
BEGIN
INSERT INTO
point2 ( elevation, geom )
VALUES
NEW.point.elevation,
st_setsrid(
st_makepoint(
st_x(NEW.point.geom),
st_y(NEW.point.geom),
NEW.point.elevation
),
25830);
RETURN new;
END;
As user30184 outlined:
It's a very common task, and there's plenty of documentation: ST_Transform.
To apply this, you need to figure out the EPSG codes of your projections.
UTM 35N probably is EPSG:32635, and your lat/lon coordinates could be anything. One of the more common ones is WGS84 as used in GPS with code EPSG:4326
The Postgis query then would be
SELECT ST_Transform(geom, 4326,32635)
If your postgis table already has the proper source SRID, you can do a simpler
SELECT ST_Transform(geom, 32635)
EDIT after you updated your question a lot:
The issue is not with ST_Transform, the issue is your query. You explicitly set all geometries to be the same. This is how UPDATE in combination with subqueries works. I really recommend you dig through the documentation a bit more to grasp this.
The solution to this issue can be found in the docs as well, by using a WHERE clause at the end. While not standard SQL, PostgreSQL allows UPDATE WHERE in order to apply subqueries to specific rows they match.
In most cases, one has a unique identifier that you can use (just hand it down from the sub queries).
In your case, depending on your data, you could use the timestamp or the lon/lat/alt itself to do so, as all these should match the same anyway.:
UPDATE info SET geom = sq.geom
FROM (
SELECT lon, lat,alt, ST_Transform(geom,32635) as geom
FROM (
SELECT lon,lat,alt, ST_setsrid(geom_v,4326) as geom
from (
select lon,lat,alt, ST_MakePoint(lon,lat,alt) as geom_v
from (
select latitude as lat, longitude as lon, altitude as alt from info order by gid asc
) as fpp
)as ftt
) as tr
) as sq
WHERE info.lon = sq.lon AND info.lat = sq.lat AND info.alt = sq.alt
However, your entire query is unnecessarily convoluted (extremely so!).
You could (and should) just use this:
UPDATE test SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(lon,lat, alt),4326),32635)
to achieve the same result with a much faster and easier-to-read query.
Best Answer
Is there a reason you have mycoords in a separate table from mypoints? If no reason I would combine them. For example do you do that because if a point changes you expect all geometries that had that point to change (so yo have a 1 to many?). If it's always a 1 to 1 I would collapse the two into one table to make your life easier.
The main issue I see with what you have is a contention issue. If you have a trigger on mypoints and mycoords, you'll probably need to have a bit that is set during the trigger to denote an update by the trigger. Otherwise you'd run into a vicious cycle with update to mypoints trying to update mycoords and mycoords update trying to update mypoints.
We have an example somehwat similar to this in appendix D of PostGIS In Action 2nd http://www.postgis.us/chapter_appendix_d_edition_2 (downloadable from there). Look for geography trigger function in the code file which both updates a field and also writes to a log table. In your case you'd want to do a lookup in mycoords and vice versa and have a similar trigger on mycoords.
The main thing you will want to consider is comparing the OLD and NEW records passed in via the trigger and comparing what was there before OLD with the NEW what got passed in, to determine whether to update the coordinates or the geometry or neither.
IF it's an INSERT, you'll need to check which fields have data to know which one to set.