0
I am migrating the Database of some applications, from Sqlserver to Postgres and several scripts use @@rowcount. There is a Postgres equivalent to @rowcount?
0
I am migrating the Database of some applications, from Sqlserver to Postgres and several scripts use @@rowcount. There is a Postgres equivalent to @rowcount?
0
You can use the instruction GET DIAGNOSTICS
after your main instruction to get the number of affected lines. For example:
DO $$
DECLARE
linhas INTEGER;
BEGIN
UPDATE tabela
SET valor = 'valor';
IF NOT FOUND THEN
RAISE NOTICE'Nenhuma linha encontrada %';
ELSIF FOUND THEN
GET DIAGNOSTICS linhas := ROW_COUNT;
RAISE NOTICE'Linhas afetadas: %', linhas;
END IF;
END $$;
39.5.5. Obtaining the Result Status
There are several Ways to determine the Effect of a command. The first method is to use the
GET DIAGNOSTICS
command, which has the form:GET DIAGNOSTICS variable = item [ , ... ];
This command Allows Retrieval of system status Indicators. Each
item
is a key word Identifying a state value to be Assigned to the specified variable (which should be of the right data type to receive it). The Currently available status items areROW_COUNT
, the number of Rows processed by the last SQL command sent to the SQL engine, andRESULT_OID
, theOID
of the last Row inserted by the Most recent SQL command. Note thatRESULT_OID
is only Useful after anINSERT
command into a table containingOIDs
.
In free translation:
39.5.5. Obtaining Result Status
There are several ways to determine the effect of a command. The first method is to use the command
GET DIAGNOSTICS
, shaped:GET DIAGNOSTICS variavel = item [ , ... ];
This command allows recovery of system status indicators. Each item
is a keyword that identifies a state value to be assigned to the specified variable (which must be of the correct data type to receive it). The currently available status items are ROW_COUNT
, the number of rows processed by the last SQL command sent to the SQL engine and RESULT_OID
, the OID
of the last row inserted by the most recent SQL command. Note that RESULT_OID
only useful after a command INSERT
in a table containing OIDs
.
Reference: @@ROWCOUNT in Postgresql 9.3.
Browser other questions tagged sql database postgresql
You are not signed in. Login or sign up in order to post.
As far as I know, there is no direct equivalent. You will probably have to create sub-querys using COUNT(*) , GROUP BY and HAVING.
– Raquel Andrade
That in the
SELECT
orUPDATE
?– Sorack
It depends on what you’re trying to do. I found these two links (below) that talk about GET DIAGNOSTICS. I don’t know how it works. You’d have to look around.
– Raquel Andrade
https://www.postgresql.org/message-id/200307250905.21817.dev%40archonet.com https://stackoverflow.com/questions/28109074/rowcount-in-postgresql-9-3
– Raquel Andrade
@Sorack Update and Delete.
– Deividson Oliveira