From: | brian <brian(at)zijn-digital(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: getting the number of rows affected by a query |
Date: | 2007-11-19 03:11:28 |
Message-ID: | 4740FEE0.2010206@zijn-digital.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2007-11-19 03:46:24 | Re: Query Performance Test |
Previous Message | Tom Lane | 2007-11-19 02:17:06 | Re: 8.3b2 XPath-based function index server crash |