simple stored procedure in Postgres

Question

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

Hambone · Accepted Answer · 2018-12-20 15:56:20Z

1

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.

answered Dec 20, 2018 at 15:56

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rodrigo Cabrera Over a year ago

I've tested it, and i got same issue in Create Sequence statement again, SQL Error: ERROR: syntax error at end of input LINE 1: CREATE SEQUENCE municipalidad.incremento START

Hambone Over a year ago

Can you list the entire function, after modifications, in your original question? I just tried on my end, and it did compile. What version are you on?

Rodrigo Cabrera Over a year ago

version 9.2 , i just needed the schema prefix on nextval('schema.incremento'), and it worked ! thanks

StephaneM · Accepted Answer · 2018-12-20 14:13:33Z

0

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

answered Dec 20, 2018 at 14:13

StephaneM

4,8791 gold badge19 silver badges34 bronze badges

1 Comment

Rodrigo Cabrera Over a year ago

actually in my code the word is incremento , I modified it only in order to publish it here.

Collectives™ on Stack Overflow

simple stored procedure in Postgres

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related