Re: reduce number of multiple values to be inserted - Mailing list pgsql-general

On Wed, 30 May 2018 08:10:05 +0800
tango ward <tangoward15@gmail.com> wrote:

> curr_pgsql.execute('''
>                    INSERT INTO student (created, modified, name,
>                                                     address, age,
> level )
>                    VALUES (current_timezone, current_timezone,
>                            'Scott', 'Malayan Village', 21, 2),
>                           (current_timezone, current_timezone,
>                            'Ben', 'Malayan Village', 21, 2),
>                           (current_timezone, current_timezone,
>                            'Scott', 'Malayan Village', 21, 2),
> 
>                           (current_timezone, current_timezone,
>                            'Andrew', 'Malayan Village', 25, 2),
>                           (current_timezone, current_timezone,
>                            'Larry', 'Malayan Village', 25, 2),
>                           (current_timezone, current_timezone,
>                            'Adam', 'Malayan Village', 25, 2),
> 
>                           (current_timezone, current_timezone,
>                            'Elisse', 'Malayan Village', 27, 2),
>                           (current_timezone, current_timezone,
>                            'Xena', 'Malayan Village', 27, 2),
>                           (current_timezone, current_timezone,
>                            'Karen', 'Malayan Village', 27, 2)
> 
> I will repeat the same process for 13 villages so that will be 117 of
> values. I would like to know if there's a way to reduce the script?
> This has to be done strictly via script.

Rather than try to cut+paste SQL it may be easier to put the data
into a flat file and have some code spit the SQL out for you:

#!/usr/bin/env perl
########################################################################
# housekeeping
########################################################################
use v5.22;
use YAML::XS    qw( Load );

########################################################################
# package variables
########################################################################

my $data 
= do
{
    # slurp the data from named files or stdin.

    local $/;
    Load <ARGV>
};

my $prefix  = <<'SQL';

/*
 * Input data for vx.y.z of student data schema.
 */

insert into student 
(
    created,
    modified,
    name,
    address,
    age,
    level
)
values
(
SQL

my $suffix = <<'SQL';
);

/*
 * end of input
 */
SQL

########################################################################
# output the SQL
########################################################################

say $prefix;

while( my ( $place, $place_valz ) = each %$data )
{
    while( my ( $nums, $namz ) = each %$place_valz )
    {
        for my $name ( @$namz )
        {
            say <<"SQL";
            (
                current_timezone,
                current_timezone,
                '$name'         ,
                '$place'        ,
                '$nums'
            )
        }
SQL
    }
}

say $suffix;

# this is not a module
0
__END__

=head1 NAME

output_sql - dump insert ... values ... from YAML

=head1 SYNOPSIS

Inputs arrive via stdin, literal, or glob-ed paths:


    output_sql << /path/to/foobar.yaml;
    output_sql /path/to/foobar.yaml;
    output_sql /path/to/*.yaml;


    gzip -dc < /path/to/bigfile.yaml | output_sql;
    gzip -dc < /path/to/bigfile.yaml | output_sql | psql;



Your data file could look like this if you want a single flat file
for all of it:

---
Malayan Village :
  21, 2 :
  - Ben
  - Scott
  25, 2 :
  - Anderew
  - Larry
  - Adam
  ...
Another Village :
 ...

Or your could break it into chunks using multiple documents within
the YAML file (notice the extra '---'):

---
Malayan Village :
  21, 2 :
  - Ben
  - Scott
  25, 2 :
  - Anderew
  - Larry
  - Adam
  ...
---
Another Village :
 ...

At which point $data, above, is an array and you get:

    for my $chunk ( @$data )
    {
        while( my ( $place, $place_valz ) = each %$chunk )
        {
            ...
        }
    }

with the added flexibility of breaking the input data into 
multiple files if needed.

-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@wrkhors.com                                    +1 888 359 3508