Обсуждение: passing text value with single quote to stored procedure
Hi all
I am currently having an issue with passing a value with a single quote in it to stored procedure
i am trying to pass this text value QA_SUMMER ' 2013_(EU/US) to stored procedure text variable. it gives me error.
Here is more detail on this
My stored procedure is something like this
CREATE OR REPLACE FUNCTION load_stage_start_v1( p_name text)
when this value is passed to the function
select load_stage_start_v1('QA_SUMMER'2013_(EU/US)');
This gives an error. Is there a way to fix this issue.
And if i am sending this question to the wrong mailing list pls let me know.
Thanks in advance
On 07/12/2013 03:05 PM, Prabhjot Sheena wrote: > Hi all > I am currently having an issue with passing a value with a > single quote in it to stored procedure > > i am trying to pass this text value QA_SUMMER ' 2013_(EU/US) to stored > procedure text variable. it gives me error. > > Here is more detail on this > > My stored procedure is something like this > > CREATE OR REPLACE FUNCTION load_stage_start_v1( p_name text) > > when this value is passed to the function > > select load_stage_start_v1('QA_SUMMER'2013_(EU/US)'); > > This gives an error. Is there a way to fix this issue. Without seeing the body of the function or the error message it is a bit of a guess, that has never stopped me before, so here are a couple of suggestions: Dollar quoting http://www.postgresql.org/docs/9.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS 4.1.2.4. Dollar-quoted String Constants so: select load_stage_start_v1($$QA_SUMMER'2013_(EU/US)$$) quote_literal http://www.postgresql.org/docs/9.2/static/functions-string.html Use quote_literal() in function to properly escape string. > > And if i am sending this question to the wrong mailing list pls let me > know. > > Thanks in advance > > -- Adrian Klaver adrian.klaver@gmail.com
On Jul 13, 2013, at 24:05, Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote: > when this value is passed to the function > > select load_stage_start_v1('QA_SUMMER'2013_(EU/US)'); > > This gives an error. Is there a way to fix this issue. Your issue has nothing to do with stored procedures. You need to escape the string properly for SQL. select load_stage_start_v1('QA_SUMMER''2013_(EU/US)'); Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.