Re: replacing single quotes

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;