CODEWITHSUNDEEP
postgresql
Rodrigo Cabrera
Rodrigo Cabrera

Reputation: 107

simple stored procedure in Postgres

I am copying data (importing)from table tmp_header into as_solution2 table, first IdNumber and Date needs to be checked on destiny table, to not copy repeated values. if date and idNumber are found in destiny table, i don't copy the row, if not found ,row is copied into table as_solution2. Source table has 800.000 records and destiny table already contains 200.000 records.

caveat: the id_solution pk in "as_solution2" table is not serial, so I created a sequence and start from the last id. 

v_max_cod_solicitud := (select max(id_solution)+1 from municipalidad.as_solution2); 

CREATE SEQUENCE increment START v_max_cod_solicitud;

this provokes an errorerror

tmp_header (id, cod_cause, idNumber , date_sol(2012-05-12), glosa_desc) as_solution2(id_solution, cod_cause, idNumber, date_sol, desc ) 

 CREATE OR REPLACE FUNCTION municipalidad.as_importar()
RETURNS integer AS 
$$
DECLARE 
v_max_cod_solicitud numeric;
id_solution numeric;

begin

v_max_cod_solicitud := (select max(id_solution)+1 from municipalidad.as_solution2); 

CREATE SEQUENCE increment START v_max_cod_solicitud;

 INSERT INTO municipalidad.as_solution2(
 id_solution,
 cod_cause,
 idNumber,
 date_sol,
 desc,

)
SELECT 
(SELECT nextval('increment')), <-- when saving i need to start from the last sequence number
cod_causingreso,
idNumber,
date_sol,
glosa_atenc,

FROM municipalidad.tmp_header as tmp_e

WHERE(SELECT count(*)
FROM municipalidad.as_solution2 as s2
WHERE s2.idNumber = tmp_e.idNumber AND s2.date_sol::date = tmp_e.date_sol::date)=0;

drop sequence increment;
return 1;
end
$$
LANGUAGE 'plpgsql'

thanks in advance

Upvotes: 1

Views: 61

Answers (2)

Hambone
Hambone

Reputation: 16377

You can brute-force the execution of the sequence with the start parameter as follows:

execute (format ('CREATE SEQUENCE incremento start %s', v_max_cod_solicitud));

Unrelated, but I think you will gain efficiencies by changing your insert to use an anti-join instead of the Where select count (*) = 0:

 INSERT INTO as_solution2(
   id_solution,
   cod_cause,
   idNumber,
   date_sol,
   description
 )
  SELECT 
    nextval('incremento'), -- when saving i need to start from the last sequence number
    cod_causingreso,
    idNumber,
    date_sol,
    glosa_atenc
  FROM tmp_header as tmp_e
  WHERE not exists (
    select null
    from as_solution2 s2
    where
      s2.idNumber = tmp_e.idNumber AND
      s2.date_sol::date = tmp_e.date_sol::date
  )

This will scale very nicely as your dataset increases in size.

Upvotes: 1

StephaneM
StephaneM

Reputation: 4899

Even though it's not listed as a reserved key word in https://www.postgresql.org/docs/9.5/sql-keywords-appendix.html, the increment in your create sequence statement might not be allowed here:

CREATE SEQUENCE increment START v_max_cod_solicitud;

As the parser expects this:

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]

It probably thinks you forgot the name

Upvotes: 0

Related Questions