All geek questions in one place

Scriptella - How to get and reuse auto-generated identifiers?

I use Scriptella for ETL operations, working with many tables referenced by auto-generated identifiers. I want to reuse these identifiers without using subqueries, this is my script fragment of the etl file:

<script connection-id="out" if="rownum>1"> SELECT nextval('SQC_CLASE') AS claseId; INSERT INTO zoologia.clase VALUES( ?claseId, ?phylumId, ?clase, ?subclase, ?infraclase, true ); SELECT nextval('SQC_ORDEN') AS ordenId; INSERT INTO zoologia.orden VALUES( ?ordenId, ?claseId, ?orden, ?suborden, true ); SELECT nextval('SQC_SUPERFAMILIA') AS superfamiliaId; INSERT INTO zoologia.superfamilia VALUES( ?superfamiliaId, ?ordenId, ?superfamilia, true ); SELECT nextval('SQC_FAMILIA') AS familiaId; INSERT INTO zoologia.familia VALUES( ?familiaId, ?superfamiliaId, ?familia, ?subfamilia, ?tribu, true ); SELECT nextval('SQC_GENERO') AS generoId; INSERT INTO zoologia.genero VALUES( ?generoId, ?familiaId, ?genero, true ); SELECT nextval('SQC_ESPECIE') AS especieId; INSERT INTO zoologia.especie VALUES( ?especieId, ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true ); </script>

This is obviously wrong because SELECT cannot be executed inside the script, right? I am not sure how to do this without subqueries. I am using PostgreSQL.

EDIT: What I want to achieve, for example, is to get the value of the auto-generated identifier used in the inserts for the first table, for its use in the inserts of the second table, because the records must be specified

+4
source share
2 answers

Thanks to the user from the Scriptella forums, this solution, the only request with all sequence values:

 <query connection-id="external"> <query connection-id="sizoo"> SELECT nextval('SQC_PHYLUM') AS phylumId , nextval('SQC_CLASE') AS claseId , nextval('SQC_ORDEN') AS ordenId , nextval('SQC_SUPERFAMILIA') AS superfamiliaId , nextval('SQC_FAMILIA') AS familiaId , nextval('SQC_GENERO') AS generoId , nextval('SQC_ESPECIE') AS especieId; <script connection-id="sizoo" if="rownum>1"> INSERT INTO zoologia.phylum VALUES( ?phylumId, ?phylum, true ); INSERT INTO zoologia.clase VALUES( ?claseId, ?phylumId, ?clase, ?subclase, ?infraclase, true ); INSERT INTO zoologia.orden VALUES( ?ordenId, ?claseId, ?orden, ?suborden, true ); INSERT INTO zoologia.superfamilia VALUES( ?superfamiliaId, ?ordenId, ?superfamilia, true ); INSERT INTO zoologia.familia VALUES( ?familiaId, ?superfamiliaId, ?familia, ?subfamilia, ?tribu, true ); INSERT INTO zoologia.genero VALUES( ?generoId, ?familiaId, ?genero, true ); INSERT INTO zoologia.especie VALUES( ?especieId, ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true ); </script> </query> </query>
+3
source

Your code initially looked completely fictitious, since nothing looks like connecting a SELECT to the next INSERT , so even if it works, you just generate an identifier and discard it. It looks like your scripting tool can automatically determine the results of SELECT column columns as variables that can be referenced in later queries; see "copy to another database" in the Scriptella tutorial . A quick look at this suggests that what you want to do may work, but for this you will need to use the nested blocks <query/> and <script/> .

The correct way to use the generated identifier sequence is one of:

 INSERT INTO zoologia.especie VALUES( nextval('SQC_ESPECIE'), ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true ); INSERT INTO zoologia.especie VALUES( DEFAULT, ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true ); INSERT INTO zoologia.especie(generoId, especie, subespecie, variedad, genero, someothercol) VALUES( ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true );

Your question initially was to reuse remote identifiers (i.e. dimensionless sequences), but it looks like you clarified it to remove this.

Update after editing + comment changed the meaning of the question:

If you are trying to use the generated single-line identifier in subsequent INSERT s, you must either:

  • Grab the identifier with INSERT ... RETURNING or by calling currval('the_id_sequence') after INSERT , save the identifier in a client variable in a scripting language and pass it to the next INSERT s; or

  • Use currval('the_id_sequence') in the VALUES list of subsequent inserts.

I have not even heard that Scriptella does not use it, so I can not help with the 1st option of using variables on the client side. I would be very surprised if he didn’t have a way to save SELECT or INSERT ... RETURNING results for future use. A very quick look suggests that this is done with the nested blocks <query/> and <script/> , but this is just a 30 second look at the tutorial.

The second option is simple. Say you just inserted:

 INSERT INTO zoologia.genero VALUES( DEFAULT, ?familiaId, ?genero, true );

and want to insert a new line into especie that has the generoId just inserted. Assuming the ID sequence for genero follows the standard PostgreSQL names, tablename_columnname_id_seq , you should use:

 INSERT INTO zoologia.especie VALUES( DEFAULT, currval('genero_generoId_seq'), ...);

Cm:

+1
source

Source: https://habr.com/ru/post/1438758/

More articles:


All Articles