passing text value with single quote to stored procedure

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general