Re[3]: Need a script that bakes INSERT script from SELECT results
От | ShuA |
---|---|
Тема | Re[3]: Need a script that bakes INSERT script from SELECT results |
Дата | |
Msg-id | E1LwuOP-0000KO-00.noface-inbox-ru@f183.mail.ru обсуждение исходный текст |
Ответ на | Re[2]: Need a script that bakes INSERT script from SELECT results (ShuA <noface@inbox.ru>) |
Список | pgsql-sql |
-----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' )
В списке pgsql-sql по дате отправления: