From: ShuA <noface@inbox.ru>
To: pgsql-sql@postgresql.org
Subject: Re[3]: Need a script that bakes INSERT script from SELECT results
Date: Thu, 23 Apr 2009 11:34:49 +0300
Message-ID: <E1LwuOP-0000KO-00.noface-inbox-ru@f183.mail.ru> (raw)
In-Reply-To: <E1LuRZV-0007OW-00.noface-inbox-ru@f242.mail.ru>
References: <E1LuRZV-0007OW-00.noface-inbox-ru@f242.mail.ru>
-----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 make insert 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'
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: noface@inbox.ru
Subject: Re: Re[3]: Need a script that bakes INSERT script from SELECT results
In-Reply-To: <E1LwuOP-0000KO-00.noface-inbox-ru@f183.mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox