Skip to main content

Ensure records

Adds records or updates data of existing ones when key fields match

Function EnsureRecords(Val Table, Val DataArray, Val KeyFields = "", Val Transaction = True, Val Connection = "", Val Tls = "") Export

ParameterCLI optionTypeRequiredDescription
Table--tableString✔Table name
DataArray--rowsArray of Structure✔An array of string data structures: Key > field, Value > field value
KeyFields--uniqueArray Of String✖Name or names of key table fields for uniqueness validation
Transaction--trnBoolean✖True > adding records to transactions with rollback on error
Connection--dbString, Arbitrary✖Existing connection or database path
Tls--tlsStructure Of KeyAndValue✖TLS settings, if necessary. See GetTlsSettings
Returns

Map Of KeyAndValue - Result of query execution

tip

UNIQUE and PRIMARY KEY fields can be specified as key fields


1C:Enterprise/OneScript code example
    Address  = "127.0.0.1";
    Login    = "bayselonarrend";
    Password = "12we...";
    Base     = "testbase1";

    TLS = True;
    Port = 5432;
    ConnectionString = OPI_PostgreSQL.GenerateConnectionString(Address, Base, Login, Password, Port);

    If TLS Then
        TLSSettings = OPI_PostgreSQL.GetTLSSettings(True);
    Else
        TLSSettings = Undefined;
    EndIf;

    Table = "test_merge";

    DataArray = New Array;

    RowStructure2 = New Structure;
    RowStructure2.Insert("id"    , New Structure("INT" , 1));
    RowStructure2.Insert("name"  , New Structure("TEXT", "Vitaly"));
    RowStructure2.Insert("age"   , New Structure("INT" , 25));
    RowStructure2.Insert("salary", New Structure("REAL", 1000.12));

    RowStructure1 = New Structure;
    RowStructure1.Insert("id"    , New Structure("INT" , 2));
    RowStructure1.Insert("name"  , New Structure("TEXT", "Lesha"));
    RowStructure1.Insert("age"   , New Structure("INT" , 20));
    RowStructure1.Insert("salary", New Structure("REAL", 200.20));

    DataArray.Add(RowStructure2);
    DataArray.Add(RowStructure1);

    KeyFields = New Array;
    KeyFields.Add("id");

    Result = OPI_PostgreSQL.EnsureRecords(Table, DataArray, KeyFields, , ConnectionString, TLSSettings);
    # JSON data can also be passed as a path to a .json file
    
    oint postgres EnsureRecords \
      --table "test_merge" \
      --rows "[{'id':{'INT':'1'},'name':{'TEXT':'Vitaly Updated'},'age':{'INT':'25'},'salary':{'REAL':'1500.5'}},{'id':{'INT':'3'},'name':{'TEXT':'Anton'},'age':{'INT':'30'},'salary':{'REAL':'3000'}}]" \
      --unique "['id']" \
      --db "postgresql://bayselonarrend:12we3456!2154@127.0.0.1:5433/testbase1" \
      --tls "{'use_tls':true,'accept_invalid_certs':true}"