Need to Printing Postgresql database console

Question

Postgresql database store procedure insert query and i need print the raise notice the input values

CREATE OR REPLACE FUNCTION new(pk character varying, u character varying,ps character varying)
      RETURNS SETOF record AS
    $BODY$
    BEGIN


    RAISE NOTICE 'PK is %','--'pk;
    RAISE NOTICE 'Username is %','--'u;
    RAISE NOTICE 'Password is %','--'ps;

    EXECUTE 'INSERT INTO table_sp("pk_id","username","password")VALUES ('''||pk||''' ,'''||u||''','''||ps||''')' ;


    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;
    ALTER FUNCTION new(character varying, character varying,character varying)
      OWNER TO postgres;

UserManualInputs:

select new('2','admin','admin');

I gave the manual input through select new('2','admin','admin') and i need print in PostgreSQL console input values using raise notice like (RAISE NOTICE 'PK is %','--'pk)

Please always mention your PostgreSQL version in questions.

Craig Ringer
– Craig Ringer

2013-07-17 07:27:21 +00:00
Commented Jul 17, 2013 at 7:27 — Craig Ringer
– Craig Ringer, Commented Jul 17, 2013 at 7:27

Pavel Stehule · Accepted Answer · 2013-07-17 07:41:01Z

2

Craig Ringer replied to you, but I have to attach a notice.

Using dynamic SQL (EXECUTE statement) in your example is very wrong idea, and badly implementation too.

use simply SQL statement only. Use dynamic SQL only if you needed!

BEGIN
  RAISE NOTICE '...';
  INSERT INTO table_sp(pk_id, username, password)
    VALUES(pk, u, ps);
END;

you used a antipattern - your code is SQL injection vulnerable. Depends on PostgreSQL version that you use, you can use a more patterns how to write secure code:

  -- very old (but secure, and more readable)
  EXECUTE 'INSERT INTO table_sp (pk_id, username, password) VALUES ('
             quote_literal(pk) || ',' quote_literal(u) || ',' || quote_literal(ps) || ')';

  -- little bit modern (8.4) - secure, readable, and fast
  EXECUTE 'INSERT INTO table_sp (pk_id, username, password) VALUES($1,$2,$3)'
    USING pk, u, ps

  -- or modern (but USING clause is better and faster for this use case)
  EXECUTE format('INSERT INTO table_sp(pk_id, username, password) VALUES(%L,%L,%L)',
             pk, u, ps)

You newer can write code:

-- very very very bad code!!!!!
EXECUTE 'some statement ''' || variable || ''' some other ';

sorry for offtopic.

answered Jul 17, 2013 at 7:41

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

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

2 Comments

Craig Ringer Over a year ago

Thanks for mentioning it. SQL injection is never off-topic. I ignored everything after the RAISE as irrelevant noise for the purpose of the question and didn't notice the horror that followed. BTW, I typically use EXECUTE format('insert into %I (%I) values ($1)', tablename, colname) USING value; that way you get to use format's smart identifier formatting and still get the parameterization offered by EXECUTE ... USING.

Pavel Stehule Over a year ago

@CraigRinger yes, a example of format in my answer is unnatural - warped for mentioned use case. Function "format" is designed exactly for usage described by you.

Craig Ringer · Accepted Answer · 2013-07-17 08:20:03Z

1

Assuming that by "PostgreSQL console" you mean psql, just make sure you SET client_min_messages = 'notice' or higher.

Your syntax probably doesn't do what you expect, though. Demonstrating using DO instead of full functions to keep things brief and simple:

regress=> DO
$$
DECLARE
    pk integer := 4;
BEGIN
    RAISE NOTICE 'PK is %','--'pk;
END;
$$;
NOTICE:  PK is --
DO

Maybe you wanted:

regress=> DO
$$
DECLARE
    pk integer := 4;
BEGIN
    RAISE NOTICE 'PK is --%',pk;
END;
$$;
NOTICE:  PK is --4
DO

?

Beware that such messages will also get logged to the PostgreSQL system log files, so you should not reveal passwords in such messages in a production app.

BTW, new is a really bad name choice for a function; I suggest something that isn't a keyword in most languages including SQL.

edited Jul 17, 2013 at 8:20

answered Jul 17, 2013 at 7:25

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

4 Comments

Kannan Over a year ago

ok this manually printed 4. but i need to print (front end value or user inputs)passing parameter in RAISE NOTICE 'PK is --%',pk;

Craig Ringer Over a year ago

@Kannan Of course. I'm just demonstrating the syntax of RAISE NOTICE; the same thing will work in your function. DO is PL/PgSQL, just like CREATE OR REPLACE FUNCTION.

Kannan Over a year ago

ok i got it, but its not printing get parameter through front end value in RAISE NOTICE.

Craig Ringer Over a year ago

@Kannan I'd need to see the full code including any schema definitions etc required to run it, then. Maybe pop it on sqlfiddle.com ? (SQLFiddle won't show NOTICEs, but it's a good way to make sure the code is self-contained and complete).

Collectives™ on Stack Overflow

Need to Printing Postgresql database console

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related