Обсуждение: Need a script that bakes INSERT script from SELECT results

Поиск
Список
Период
Сортировка

Need a script that bakes INSERT script from SELECT results

От
ShuA
Дата:
I'm a novice to PL/pgSQL, and I'm curious, how to write function, that wraps SELECT results into INSERT script. 

The functionality I need is about to replicate table data into remote DB.

I have tried dblink_build_insert(), but it requires primary key on tuple to create INSERT script from. I would prefer
filterclause to primary key, to generate either multiple inserts or one multi-row insert.
 

Could someone post an example how to LOOP through row fields to wrap them into 'VALUES(...,,)' list?

Thanks,
Olksy



Re: Need a script that bakes INSERT script from SELECT results

От
John DeSoi
Дата:
On Apr 16, 2009, at 7:29 AM, ShuA wrote:

> Could someone post an example how to LOOP through row fields to wrap  
> them into 'VALUES(...,,)' list?


If you declare record or table row types, you can insert the values  
using (rec.*), something like this:


create or replace function test ()
returns void as $$
declarerec record;
beginfor rec in select * from whatever loop    insert into some_table values (rec.*);end loop;
end;
$$ language plpgsql;






John DeSoi, Ph.D.






Re[2]: Need a script that bakes INSERT script from SELECT results

От
ShuA
Дата:
-----Original Message-----
From: John DeSoi <desoi@pgedit.com>
To: ShuA <noface@inbox.ru>
Date: Thu, 16 Apr 2009 08:25:15 -0400
Subject: Re: [SQL] Need a script that bakes INSERT script from SELECT results

> On Apr 16, 2009, at 7:29 AM, ShuA wrote:
>
> > Could someone post an example how to LOOP through row fields to wrap
> > them into 'VALUES(...,,)' list?
>
>
> If you declare record or table row types, you can insert the values
> using (rec.*), something like this:
>
>
> create or replace function test ()
> returns void as $$
> declare
>     rec record;
> begin
>     for rec in select * from whatever loop
>         insert into some_table values (rec.*);
>     end loop;
> end;
> $$ language plpgsql;
>
> John DeSoi, Ph.D.

The functionality I need is about to replicate table data into remote DB.

>     for rec in select * from whatever loop
>         insert into some_table values (rec.*);
>     end loop;

^^^ that is not my case, unfortunately.

The next, what is supposed to do with baked INSERT statements, is push them thru dblink_exec(), as text param, to make
insertworks on remote DB. 

Olksy




Re[3]: Need a script that bakes INSERT script from SELECT results

От
ShuA
Дата:
-----Original Message-----
From: ShuA <noface@inbox.ru>
To: John DeSoi <desoi@pgedit.com>
Date: Thu, 16 Apr 2009 16:24:05 +0300
Subject: Re[2]: [SQL] Need a script that bakes INSERT script from SELECT results
>
> -----Original Message-----
> From: John DeSoi <desoi@pgedit.com>
> To: ShuA <noface@inbox.ru>
> Date: Thu, 16 Apr 2009 08:25:15 -0400
> Subject: Re: [SQL] Need a script that bakes INSERT script from SELECT results
>
> > On Apr 16, 2009, at 7:29 AM, ShuA wrote:
> >
> > > Could someone post an example how to LOOP through row fields to wrap
> > > them into 'VALUES(...,,)' list?
> >
> >
> > If you declare record or table row types, you can insert the values
> > using (rec.*), something like this:
> >
> >
> > create or replace function test ()
> > returns void as $$
> > declare
> >     rec record;
> > begin
> >     for rec in select * from whatever loop
> >         insert into some_table values (rec.*);
> >     end loop;
> > end;
> > $$ language plpgsql;
> >
> > John DeSoi, Ph.D.
>
> The functionality I need is about to replicate table data into remote DB.
>
> >     for rec in select * from whatever loop
> >         insert into some_table values (rec.*);
> >     end loop;
>
> ^^^ that is not my case, unfortunately.
>
> The next, what is supposed to do with baked INSERT statements, is push them thru dblink_exec(), as text param, to
makeinsert works on remote DB. 
>
> Olksy

Finally, I get down to PL/Perl scripting, and created the next pieces of code that work as I stated above.

Code piece #1
-------------

CREATE OR REPLACE FUNCTION build_insert(relation text, where_filter text)
  RETURNS text AS $$

# converts value to sql literal
my $to_literal = sub {

  my $value = shift;

  return 'NULL' if !defined $value;
  $value =~ s/'/''/g;   # escape quotes,
  return "'$value'";    # return quoted literal
};

my ($relation, $where_filter) = @_;

$rv = spi_exec_query("SELECT * FROM $relation WHERE $where_filter");
return '' if !$rv->{processed};

# columns comma separated list
my $columns;
# values lists formatted for INSERT
my @values;

my $nrows = $rv->{processed};
foreach my $rn (0 .. $nrows - 1) {

  my $rowref = $rv->{rows}[$rn];

  # doesnt support column names like "ja hitrovijebaniy column".
  # assume columns order is the same for all rows.
  $columns = join ', ' => keys %$rowref;

  my $tuple_values = join ', ' => map($to_literal->($_), values %$rowref);
  push @values, "($tuple_values)";
}

return sprintf
  'INSERT INTO %s (%s) VALUES %s'
  ,$relation
  ,$columns
  ,join ', ' =>  @values
  ;

$$ LANGUAGE plperl;

Code piece #2
-------------

CREATE OR REPLACE FUNCTION dblink_replica(conn text, relation text, where_filter text)
  RETURNS text AS $$
DECLARE
  insert_sql text;
BEGIN
  SELECT build_insert(relation, where_filter) INTO insert_sql;
  IF '' = insert_sql THEN
    RETURN '';
  ELSE
    -- dblink_exec() will raise error if fail
    RETURN (SELECT dblink_exec(conn, insert_sql));
  END IF;
END;
$$ LANGUAGE plpgsql;

Code piece #3 (use case)
------------------------
SELECT tru_dblink_replica(
    'conname1',
    'units'
    'unit_id = $unit_id'
    )