CODEWITHSUNDEEP
sqlpostgresql
thewayup
thewayup

Reputation: 1155

Trouble understanding how to use EXCEPTION with BEGIN and END

I am new to Postgres and I am trying to create a test for when the result of the following query returns null, but I can't seem get to get it right. I keep getting the following error

ERROR:  syntax error at or near "WITH"
LINE 20: WITH expected (tran_id, sell_chan, total) AS (VALUES (3, 'St...

when I try and run the following code. Note that if I remove the BEGIN and END and the EXCEPTION parts, everything else runs fine an get the expected null query.

-- create test database
DROP TABLE IF EXISTS test_sales;
CREATE TABLE test_sales(
  tran_id INT PRIMARY KEY,
  chan_c VARCHAR(1),
  total FLOAT
);


-- populate test database
INSERT INTO test_sales VALUES (1, 'S', 99.99);
INSERT INTO test_sales VALUES (2, 'O', 99.99);
INSERT INTO test_sales VALUES (3, 'S', 100);
INSERT INTO test_sales VALUES (4, 'O', 100);
INSERT INTO test_sales VALUES (5, 'P', 99.99);
INSERT INTO test_sales VALUES (6, 'P', 100);

BEGIN
WITH expected (tran_id, sell_chan, total) AS (VALUES (3, 'Store', 100), (4, 'Online',100), (6,'Other',100))
SELECT tran_id, sell_chan, total INTO my_temp FROM (SELECT * FROM expected) AS q1 EXCEPT (SELECT tran_id,
                CASE chan_c
                WHEN 'S' THEN 'Store'
                WHEN 'O' THEN 'Online'
                ELSE 'Other'
                END sell_chan,
                total
                FROM test_sales AS q2 WHERE q2.total>=100) EXCEPTION WHEN NO_DATA_FOUND THEN NULL; 


END;

Upvotes: 0

Views: 55

Answers (1)

sticky bit
sticky bit

Reputation: 37472

  • Anonymous blocks have to be done with DO;
  • To insert into a temporary table you first have to create it.
  • The syntax is INSERT INTO TABLE <table> SELECT ... not SELECT ... INTO <table>.
  • EXCEPTION needs a block it belongs to.
  • Side note regarding your comments: A table is not a database!

Try:

-- create test table
DROP TABLE IF EXISTS test_sales;
CREATE TABLE test_sales(
  tran_id INT PRIMARY KEY,
  chan_c VARCHAR(1),
  total FLOAT
);


-- populate test table
INSERT INTO test_sales VALUES (1, 'S', 99.99);
INSERT INTO test_sales VALUES (2, 'O', 99.99);
INSERT INTO test_sales VALUES (3, 'S', 100);
INSERT INTO test_sales VALUES (4, 'O', 100);
INSERT INTO test_sales VALUES (5, 'P', 99.99);
INSERT INTO test_sales VALUES (6, 'P', 100);

DROP TABLE IF EXISTS my_temp;
CREATE TEMPORARY TABLE my_temp AS SELECT * FROM test_sales WHERE 1 <> 1;

DO
$$
BEGIN
  BEGIN
    WITH expected (tran_id, sell_chan, total) AS (VALUES (3, 'Store', 100), (4, 'Online',100), (6,'Other',100))
    INSERT INTO my_temp
    SELECT tran_id, sell_chan, total  FROM (SELECT * FROM expected) AS q1 EXCEPT (SELECT tran_id,
                    CASE chan_c
                    WHEN 'S' THEN 'Store'
                    WHEN 'O' THEN 'Online'
                    ELSE 'Other'
                    END sell_chan,
                    total
                    FROM test_sales AS q2 WHERE q2.total>=100);
  EXCEPTION WHEN NO_DATA_FOUND THEN
    NULL;
  END;
END;
$$
LANGUAGE plpgsql;

Upvotes: 1

Related Questions