Postgres Pro
Downloads
Home   >   mailing lists

Thread: replacing single quotes

replacing single quotes

From
Ow Mun Heng
Date:
Input is of form

'ppp','aaa','bbb'

I want it to be stripped of quotes to become

ppp,aaa,bbb

escaping the quote would work but it means I will have to do some magic
on the input as well to escape it prior to replacing it.

select replace('AB\'A','\'','C') this works

Can I buy a clue here?


oh.. I;ve also tried to use quote_literal but no go..

Re: replacing single quotes

From
"Albe Laurenz"
Date:
Ow Mun Heng wrote:
> Input is of form
>
> 'ppp','aaa','bbb'
>
> I want it to be stripped of quotes to become
>
> ppp,aaa,bbb
>
> escaping the quote would work but it means I will have
> to do some magic on the input as well to escape it prior
> to replacing it.
>
> select replace('AB\'A','\'','C') this works
>
> Can I buy a clue here?

Sorry, all the advisives are free here.

I'm a little confused that you think that you will have to
escape single quotes in the input.
What is your use case? Normally the input is in some variable
in some programming language.
Escaping single quotes is only for string literals.

Like this:

CREATE FUNCTION rep(v text) RETURNS text
  LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT replace($1, '''', '')$$;

BTW, I would use '' instead of \' to escape single quotes.
It is safer and standard compliant.

Yours,
Laurenz Albe

Re: replacing single quotes

From
Ow Mun Heng
Date:
On Wed, 2007-10-10 at 09:11 +0200, Albe Laurenz wrote:
> Ow Mun Heng wrote:
> > Input is of form
> >
> > 'ppp','aaa','bbb'
> >
> > I want it to be stripped of quotes to become
> >
> > ppp,aaa,bbb
> >
> > escaping the quote would work but it means I will have
> > to do some magic on the input as well to escape it prior
> > to replacing it.
> >
> > select replace('AB\'A','\'','C') this works
> >
> > Can I buy a clue here?
>
> Sorry, all the advisives are free here.
>
> I'm a little confused that you think that you will have to
> escape single quotes in the input.
> What is your use case? Normally the input is in some variable
> in some programming language.
> Escaping single quotes is only for string literals.
>
> Like this:
>
> CREATE FUNCTION rep(v text) RETURNS text
>   LANGUAGE sql IMMUTABLE STRICT AS
> $$SELECT replace($1, '''', '')$$;
>
> BTW, I would use '' instead of \' to escape single quotes.
> It is safer and standard compliant.

The input is for an SRF which accepts an array..

eg:
select * from foo(date1,date2,'{aaa,bbb,ccc}')

where the function goes..
create function foo(timestamp, timestamp, foo_list text[]) returns setof
results as
...
where foo_list = any (foo_list)
..

Re: replacing single quotes

From
"Albe Laurenz"
Date:
Ow Mun Heng wrote:
>>> Input is of form
>>>
>>> 'ppp','aaa','bbb'
>>>
>>> I want it to be stripped of quotes to become
>>>
>>> ppp,aaa,bbb
>>
>> I'm a little confused that you think that you will have to
>> escape single quotes in the input.
>> What is your use case? Normally the input is in some variable
>> in some programming language.
>> Escaping single quotes is only for string literals.
>
> The input is for an SRF which accepts an array..
>
> eg:
> select * from foo(date1,date2,'{aaa,bbb,ccc}')
>
> where the function goes..
> create function foo(timestamp, timestamp, foo_list text[])
> returns setof
> ...

Yes, but where does '{aaa,bbb,ccc}' come from?
I assume that this string literal is only an example
that you use to describe how the function is called.

In reality you will have varying values for the
foo_list function argument. So you'll store it in some
kind of variable, right?

In which programming language do you write?

Yours,
Laurenz Albe

Re: replacing single quotes

From
Ow Mun Heng
Date:
On Wed, 2007-10-10 at 10:46 +0200, Albe Laurenz wrote:
> Ow Mun Heng wrote:
> >>> Input is of form
> >>>
> >>> 'ppp','aaa','bbb'
> >>>
> >>> I want it to be stripped of quotes to become
> >>>
> >>> ppp,aaa,bbb
> >>
> >> I'm a little confused that you think that you will have to
> >> escape single quotes in the input.
> >> What is your use case? Normally the input is in some variable
> >> in some programming language.
> >> Escaping single quotes is only for string literals.
> >
> > The input is for an SRF which accepts an array..
> >
> > eg:
> > select * from foo(date1,date2,'{aaa,bbb,ccc}')
> >
> > where the function goes..
> > create function foo(timestamp, timestamp, foo_list text[])
> > returns setof
> > ...
>
> Yes, but where does '{aaa,bbb,ccc}' come from?
> I assume that this string literal is only an example
> that you use to describe how the function is called.

It's an input from user. However, the input is of the form

'aaa','bbb','ccc'

which needs to be stripped down to the form

aaa,bbb,ccc



>
> In reality you will have varying values for the
> foo_list function argument. So you'll store it in some
> kind of variable, right?

Yea.. and that variable is called foo_list.


>
> In which programming language do you write?

plpgsql


(This is the SRF) which is joined to another query which uses the
where foo_list in ('aaa','bbb','ccc') syntax which is different from the
Array Syntax.


CREATE OR REPLACE FUNCTION foo(fromdate timestamp without time zone,
todate timestamp without time zone, code text[])
  RETURNS SETOF trh_amb AS
$BODY$

DECLARE
rec RECORD;

BEGIN
    FOR rec IN
    SELECT
    ...
    ...

    FROM foo_table_a a
    INNER JOIN foo_table_b b
        ON a.a = b.a
    WHERE a.date_time BETWEEN fromdate AND todate
      AND a.foo_list = any (code)

    LOOP
    RETURN NEXT rec;
    END LOOP;
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Re: replacing single quotes

From
"Albe Laurenz"
Date:
Ow Mun Heng wrote:
>>>>> Input is of form
>>>>>
>>>>> 'ppp','aaa','bbb'
>>>>>
>>>>> I want it to be stripped of quotes to become
>>>>>
>>>>> ppp,aaa,bbb
>>>
>>> The input is for an SRF which accepts an array..
>>>
>>> where the function goes..
>>> create function foo(timestamp, timestamp, foo_list text[])
>>> returns setof
>>> ...
I said:
>> In reality you will have varying values for the
>> foo_list function argument. So you'll store it in some
>> kind of variable, right?
>>
>> In which programming language do you write?
Answer:
> plpgsql

Ok, we're coming closer.

What I mean:

In which programming language is the *call* to
function foo()? Java? PHP? PL/pgSQL?
Could you tell me the exact statement that you use
to call foo()?

Why do I ask this? An example:

If you use embedded SQL to call the function, and the
input is stored in the host variable "inpstr", then the answer
would be:

EXEC SQL DECLARE c CURSOR FOR SELECT * FROM foo(localtimestamp,
localtimestamp, string_to_array(replace(:inpstr, '''''', ''), ','));

Yours,
Laurenz Albe