0

I am storing data which includes single and double quotes in a column which has a type of text.

I have a function that returns that column but it always returns the double quote character being doubled up. I tried to escape the character \" but that just returned \"". I have the same process working in MySQL well. I am trying to move to Postgres for greater scaling and stability but it seems it does not work well. I have noticed in pgAdmin when I look at the data output, it seems to show the data correctly so I am wondering what is being done there for it to work correctly?

Also I am getting a (" at the start of the returned value and ") at the end.

Can someone help please?

Improve this question
7
  • 1
    What language do you use? Wich framework? Can you give more information or share the function you mentioned? Commented May 2, 2020 at 2:15
  • Also please provide some actual data, results (as formatted text - no images), and queries you have tried. Commented May 2, 2020 at 5:29
  • 1
    Postgres does not duplicate them and in SQL there is no need to escape double quotes. This is something in your application code that does this. dbfiddle.uk/… Commented May 2, 2020 at 7:46
  • Welcome! As others already pointed out good ... pointers, I wanted to touch on scaling. Do you only plan to increase read capacity but not compute? In other words, if you foresee a need for sharding in the next 5 years, I strongly suggest a sharded cluster such as MariaDB. It is also much closer to MySQL and will cost less in migration/training, too. Commented May 2, 2020 at 9:05
  • I am using php with postgreq, I hard code - no frameworks. FUNCTION public.getpage(integer) RETURNS page LANGUAGE 'plpgsql' VOLATILE COST 100 AS $BODY$declare inPage alias for $1; outPage page; BEGIN select into outPage components_main.compdoc||components_content.compcontent||components_main.compbodyf from framework left join components_main on components_main.cid=framework.bodyid left join components_content on components_content.cid=framework.contentid where framework.cid = inPage; return outPage; end;$BODY$; Commented May 2, 2020 at 15:40

1 Answer 1

Reset to default
1

Use pg_escape_string to escape quote characters in your string.

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

1 Comment

Thanks for the suggestion but the data is not the issue. I have tried running the same query that is within the Function directly rather than using the stored function and my data is returned perfectly. So my thought is that the stored Function seems to apply strange processing on the way out to give php a result which is doubling the double quotes and adding those pesky (" ") at the start and finish. Any ideas?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.