POSTGRESQL = error data return from SPLIT_PART in trigger function

Question

I have a series of query that used to generate a unique value called uwi when I enter a Decimal degree coordinate (longitude and latitude). its combination from several data. first I need to convert DD coordinate to DMS format I reproduce this in dummy table named logic :

INSERT INTO public."logic"("DMSS")
SELECT (ST_AsLatLonText('POINT (' || cast(surface_longitude as text)|| ' '
||cast(surface_latitude as text) ||')', 'D M S.SSS C')) from well;

result


st_aslatlontext             |
----------------------------|
8 58 4.431 S 77 26 8.335 E  |
9 21 16.812 S 68 14 11.237 W|
7 20 43.531 S 88 27 23.851 E|

Then I need to arrange the coordinate to this format using split_part: select SPLIT_PART(logic."DMSS"::text, ' ', 4) ||SPLIT_PART(logic."DMSS"::text, ' ', 1) ||SPLIT_PART(logic."DMSS"::text, ' ', 2) ||SPLIT_PART(logic."DMSS"::text, ' ', 3) ||'.' ||SPLIT_PART(logic."DMSS"::text, ' ', 8) ||SPLIT_PART(logic."DMSS"::text, ' ', 5) ||SPLIT_PART(logic."DMSS"::text, ' ', 6) ||SPLIT_PART(logic."DMSS"::text, ' ', 7) from logic ; result ?column? | --------------------| S8584431.E77268335 | S92116812.W681411237| S72043531.E882723851|

combining with other data it should be like this to dummy table LOGIC :


    UPDATE logic
    SET "uwi" = '062.0001.00.'||SPLIT_PART(logic."DMSS"::text, ' ', 4)||SPLIT_PART(logic."DMSS"::text, ' ', 1)||SPLIT_PART(logic."DMSS"::text, ' ', 2)||regexp_replace(SPLIT_PART(logic."DMSS"::text, ' ', 3),'(\d)\.(\d)','\1\2')||'.'||SPLIT_PART(logic."DMSS"::text, ' ', 8)||SPLIT_PART(logic."DMSS"::text, ' ', 5)||SPLIT_PART(logic."DMSS"::text, ' ', 6)||regexp_replace(SPLIT_PART(logic."DMSS"::text, ' ', 7),'(\d)\.(\d)','\1\2');

FINAL RESULT uwi | ---------------------------------| 062.0001.00.S8584431.E77268335 | 062.0001.00.S92116812.W681411237 | 062.0001.00.S72043531.E882723851 |

but when I reproduce this in the table named well as trigger AFTER INSERT function


DECLARE 

    BEGIN
         IF tg_op='INSERT' THEN 
       CREATE TEMP TABLE dms_temp ON COMMIT DROP AS
       SELECT (ST_AsLatLonText('POINT (' || cast(new.surface_longitude as text)|| ' ' ||     cast(new.surface_latitude as text) ||')', 'D M S.SSS C')) 
        from well w;

INSERT INTO PUBLIC."well"( uwi ) 
SELECT 
'062.' 
|| CASE 
            WHEN w.primary_source = 'PEP' THEN '0001' ELSE'0002' 
     END 
        || '.' 
        || '00.' 
        || SELECT SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 4) 
                ||SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 1) 
                ||SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 2)
                ||regexp_replace(SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 3),'(\d)\.(\d)','\1\2') 
                ||'.'
                ||SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 8) 
                ||SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 5) 
                ||SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 6) 
                ||regexp_replace(SPLIT_PART(dms_temp."st_aslatlontext"::text, ' ', 7),'(\d)\.(\d)','\1\2') 
                from dms_temp)
        FROM well as w where ; 
 END IF;
 RETURN NEW;
drop table dms_temp;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

I get an error more than one row returned by a subquery used as an expression this table is the main data table as reference for other table

POSTGRESQL = error data return from SPLIT_PART in trigger function

0 Answers