Re: getting the number of rows affected by a query

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