Lists: | pgsql-general |
---|
From: | Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | passing text value with single quote to stored procedure |
Date: | 2013-07-12 22:05:42 |
Message-ID: | CAOf_bQYJ2JAym1wNQ3=iZdHNabawBGVUAfu-n9fGZAjJWg8BXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: passing text value with single quote to stored procedure |
Date: | 2013-07-13 00:11:35 |
Message-ID: | 51E09B37.1080002@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)klaver(at)gmail(dot)com
From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: passing text value with single quote to stored procedure |
Date: | 2013-07-13 09:46:20 |
Message-ID: | AA0ED26E-D8C4-4F9D-88DD-C81384394AA3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Jul 13, 2013, at 24:05, Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)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.