Ow Mun Heng wrote:
> Turns out this is a 2 part question, for which I have 1 solved.
>
> 1. using perl DBI to pull from MSSQL to PG..
> --> I found out I can use
> my $ins_rows = $dbh_pg->do($query2) or die "prepare failed
> $DBI::errstr";
>
> 2. using pure SQL (via pgagent jobs) to pull. This is the one which I've
> yet to be able to solve w/o writing a function and using GET DIAGNOSTICS
> ROW COUNT.
> --> Is one able to use variables in pure SQL ? (eg: undel psql?)
>
You could use PL/Perl's $_SHARED construct:
CREATE OR REPLACE FUNCTION set_id(name text, val INT4)
RETURNS text AS $$
if ($_SHARED{$_[0]} = $_[1])
{
return 'ok';
}
else
{
return "can't set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_id(name text)
RETURNS INT4 IMMUTABLE AS $$
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;
I use it occasionally when i need to save some insert ID for something.
You could do the same thing with your row count.
SELECT set_id('the_row_count', CAST(currval('x') AS INT))
SELECT get_id('the_row_count') AS the_row_count;
or:
SELECT CAST(get_id('the_row_count') AS INT) AS the_row_count;
Where 'x' represents your row count, however you get that.
If you're using pg >= 8.2 there's a RETURNING clause for DELETE. I'm not
sure if that's what you want.
brian
