Search Postgresql Archives

Re: replacing single quotes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux