5

Is it possible to print to either the console window or the output window when debugging Postgres SQL using DBeaver?

If so...HOW? Thanks!

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
learnedOnPascal
  • 103
  • 2
  • 12

3 Answers3

6

You can use in your code:

RAISE NOTICE 'log value %', value ; 

or other debug print and view in "Output" window in DBeaver (second from the bottom icon in the left sql editor panel)

szub
  • 61
  • 1
  • 1
3

You can debug the poor mans way with a Table called PrintOut and a column called messages:

do $$
declare 
ppllastActivity date;
BEGIN
SELECT lastactivity into ppllastActivity FROM People WHERE email = 'a@abc.com';

IF COALESCE (ppllastActivity, '1900-01-01') = '1900-01-01' THEN
INSERT INTO printout (message) VALUES ('step 1');
END if;

END; $$

SQL to create the sequence, table and timestamp:

-- DROP SEQUENCE public.printout_id_seq;

CREATE SEQUENCE public.printout_id_seq
    INCREMENT BY 1
    MINVALUE 1
     MAXVALUE 9223372036854775807;
    
-- DROP TABLE public.printout;

CREATE TABLE public.printout (
     id INTEGER DEFAULT NEXTVAL('printout_id_seq') NOT NULL,
     message varchar NOT NULL
);

ALTER TABLE printout ADD COLUMN created_at TIMESTAMP;
ALTER TABLE printout ALTER COLUMN created_at SET DEFAULT now();

Then refresh the PrintOut table to see debug messages.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • Not necessary but we can add ALTER TABLE printout ADD CONSTRAINT printout_pk PRIMARY KEY (id); to the table definition. On the other hand. Concerning the tip. What is the inconvenient with comments? – Atreide Jun 11 '21 at 09:55
  • Problem I find. I execute the function and I only get table printout populated when it does execute with no errors, I guess there is an "auto rollback" active on error _Don´t know if there is any chance to configure it though_ It is still big help as I can comment the parts that produce the error and still follow the data, or decisions taken by the function. – Atreide Jun 11 '21 at 09:57
2

That option is not avaiable in DBeaver. You can use some paid software to debug the SP and functions. While you can use pgAdmin to debug if you have the "SuperUser" privilege.

Ralf Stubner
  • 26,263
  • 3
  • 40
  • 75