WHY DON'T I GET TOO MANY ROWS?

[Thomas Morgan]

We are in the process of evaluating our system for migration from Oracle to Postgresql and one of the differences is how TOO_MANY_ROWS behaves.  According to a post I found, it claimed that it would work exactly as Oracle if I added the word STRICT before the INTO clause, so I created following scenario which does not see to work:

template1=# CREATE TABLE ZTM_1(A VARCHAR2(1));
CREATE TABLE
template1=#
template1=# CREATE OR REPLACE PROCEDURE Get_Too_Many_Rows() LANGUAGE plpgsql AS $$
template1$# DECLARE
template1$#   v_into VARCHAR2(4000);
template1$# BEGIN
template1$#   DELETE FROM ZTM_1;
template1$#   INSERT INTO ZTM_1(A) VALUES('A');
template1$#   INSERT INTO ZTM_1(A) VALUES('B');
template1$#   --
template1$#   SELECT A STRICT INTO v_Into FROM ZTM_1;
template1$#   --
template1$#   RAISE NOTICE 'COMPLETED.';
template1$# EXCEPTION
template1$#   WHEN TOO_MANY_ROWS THEN
template1$#     RAISE EXCEPTION 'Too Many';
template1$# END;
template1$# $$;
CREATE PROCEDURE
template1=# CALL Get_Too_Many_Rows();
NOTICE:  COMPLETED.
CALL

template1=# SELECT * FROM ZTM_1;
 a
---
 A
 B

Any ideas?

Thanks

[Christophe Pettus]

> On Jan 13, 2024, at 13:08, Thomas Morgan <thomasmorg...@gmail.com> wrote:
>
> We are in the process of evaluating our system for migration from Oracle to Postgresql and one of the differences is how TOO_MANY_ROWS behaves. According to a post I found, it claimed that it would work exactly as Oracle if I added the word STRICT before the INTO clause, so I created following scenario which does not see to work:

The STRICT keyword follows the INTO. Why you didn't get a syntax error otherwise is an excellent question, though.

xof=# CREATE TABLE ZTM_1(A VARCHAR(1));
CREATE TABLE
xof=# CREATE OR REPLACE PROCEDURE Get_Too_Many_Rows() AS $$
DECLARE
v_into VARCHAR(4000);
BEGIN
DELETE FROM ZTM_1;

INSERT INTO ZTM_1(A) VALUES('A');

INSERT INTO ZTM_1(A) VALUES('B');

--
SELECT A INTO STRICT v_into FROM ZTM_1;
--
RAISE NOTICE '%', v_into;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'Too Many';
END;
$$ LANGUAGE plpgsql;
CREATE PROCEDURE
xof=# CALL Get_Too_Many_Rows();
ERROR: Too Many
CONTEXT: PL/pgSQL function get_too_many_rows() line 14 at RAISE

[Christophe Pettus]

> On Jan 13, 2024, at 16:10, Christophe Pettus <x...@thebuild.com> wrote:
>
> Why you didn't get a syntax error otherwise is an excellent question, though.

Oh, of course. If you put it before INTO, you are giving an alias to the column of STRICT:

xof=# select a strict from ztm_1;
strict
--------
(0 rows)