How insert quotes in postgresql

Question

How to insert double quotes & single quotes in postgres using stored procedure.

I have create the table as :
CREATE TABLE public.test (
  id INT, 
  name varchar(50),
  lname varchar(100)
) 
WITH (oids = false);

2 . Created the stored procedure to insert into that table.

CREATE OR REPLACE FUNCTION test_insert( p_array in TEXT[] ) RETURNS TEXT AS
$$
DECLARE
  arrstrMixrecordData ALIAS FOR $1;
  plid integer = 0;
  pid integer = arrstrMixrecordData[1];
  pname varchar = arrstrMixrecordData[2];
  plname varchar = arrstrMixrecordData[3];
BEGIN
  INSERT INTO
    test(id, name, lname)
  values
    (pid, pname, plname);
  plid = ( SELECT id from test ORDER BY id desc limit 1 );
  plid = plid + 1;
  RETURN plid;
END;
$$
  LANGUAGE plpgsql;

3.inserted data using this query:

SELECT * from test_insert( '{1,abc,pqr}'::TEXT[] );

It works !!!

4.All below combination failed . When double quotes comes in single quotes and wise versa

SELECT * from test_insert( '{1,ab"c,pqr}'::TEXT[] );
SELECT * from test_insert( "{1,ab'c,pqr}"::TEXT[] );
SELECT * from test_insert( "{1,ab"c,pqr}"::TEXT[] );
SELECT * from test_insert( '{1,ab'c,pqr}'::TEXT[] );

how to insert above data using stored procedure??

Failed with **what error** exactly? – Craig Ringer Sep 16 '14 at 09:47 — Craig Ringer, Sep 16 '14 at 09:47

score 0 · Answer 1 · answered Sep 16 '14 at 06:20

0

I didn't fully read your question, but refer to quote_ident and quote_literal in the Postgres Documentation for possible quoting techniques that may suit your needs.

Otherwise, you may want something like:

SELECT * from test_insert( E'{1,ab\'c,pqr}'::TEXT[] );

answered Sep 16 '14 at 06:20

vol7ron

40,809
21
119
172

You should *strongly* prefer to use the `format` function with the `%I` and `%L` specifiers instead of `quote_literal` and `quote_ident` now. However, there's no dynamic SQL (`EXECUTE`) here, so no quoting should be required. – Craig Ringer Sep 16 '14 at 09:45
1

This is also just not the problem. It's nothing to do with the procedure. The user just doesn't understand how to escape quotes. – Craig Ringer Sep 16 '14 at 09:49
Yeah. Again I provided the answer on the way to my destination without reading the question (maybe I shouldn't have) but the second part of my answer services that. -- Not as familiar with the %* placeholders. I know the quote functions are backwards compatible, but I'll have to look into those - thanks – vol7ron Sep 16 '14 at 12:19
Unable to insert quotes in stored procedure text array – Harshad Sep 18 '14 at 05:58
Using double quotes on the outside is wrong and did you see the part in the answer about `E'ab\'c' – vol7ron Sep 18 '14 at 12:46

How insert quotes in postgresql

1 Answers1