Print prepared dynamic query with result set in Postgresql function using DBeaver

Question

I have the function in which I have prepared dynamic query, which I want print in output window before executing it.

Note: In the following example I have just add simple select statement to understand the requirement.

Sample tables:

create table t1
(
col1 int,
col2 text
);
insert into t1 values(1,'Table T1');
insert into t1 values(2,'Table T1');

create table t2
(
col1 int,
col2 text
); 
insert into t2 values(1,'Table T2');
insert into t2 values(2,'Table T2');

Function:

create or replace function fn_testing(tbl_Name text)
returns table(col1 int,col2 text) as 
$$
begin
   return query execute 'select col1,col2 from '||tbl_name||'';
end;
$$
language plpgsql;

Function call:

select * from fn_testing('t2');

I want to print following in message window with result set too in result window:

select col1,col2 from t1;

sticky bit · Accepted Answer · 2021-06-21 12:16:37Z

1

You can use RAISE NOTICE for messages.

CREATE OR REPLACE FUNCTION fn_testing
                           (_tbl_name name)
                           RETURNS TABLE
                                   (col1 integer,
                                    col2 text)
AS
$$
DECLARE
  _query text;
BEGIN
  _query := format('SELECT col1, col2 FROM %I;', _tbl_name);
  RAISE NOTICE '%', _query;
  RETURN QUERY EXECUTE _query;
END;
$$
LANGUAGE plpgsql;

Note: There's a special type, name, for identifiers. And to prevent SQL injection or errors you should make sure the dynamic identifiers are properly quoted. You can use format() with %I for that.

answered Jun 21, 2021 at 12:16

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Print prepared dynamic query with result set in Postgresql function using DBeaver

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related