Is there an equivalent to SQLSERVER’s @ROWCOUNT in Postgres?

Asked

Viewed 168 times

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?

sql database postgresql

  • As far as I know, there is no direct equivalent. You will probably have to create sub-querys using COUNT(*) , GROUP BY and HAVING.

  • That in the SELECT or UPDATE?

  • 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.

  • https://www.postgresql.org/message-id/200307250905.21817.dev%40archonet.com https://stackoverflow.com/questions/28109074/rowcount-in-postgresql-9-3

  • @Sorack Update and Delete.

1 answer

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 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 is only Useful after an INSERT command into a table containing OIDs.

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.