Re: [GENERAL] how to convert a string array to a string. fct array_to_string seem to work only for INT array??

[George Weaver]

On Thursday, May 10, 2007 6:07 AM David Gagnon wrote
I have a string array(Compte[]) and I need to create the following string 
statement to populate a temporary table

statement := ' INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                   SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                   FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND 
CR.CRYPNUM = CS.CSYPNUM
                   WHERE CRYPNUM = ' || quote_literal(companyId) || '
                        AND CRDATE  <= CURRENT_DATE
            AND CSGLNUM IN {'  || array_to_string(Compte, ',') || '}';

   EXECUTE statement;

For now I get :

INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                   SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                   FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND 
CR.CRYPNUM = CS.CSYPNUM
                   WHERE CRYPNUM = 'M'
                        AND CRDATE  <= CURRENT_DATE
            AND CSGLNUM IN {cpt1, cpt2}

But I want:

INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                   SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                   FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND 
CR.CRYPNUM = CS.CSYPNUM
                   WHERE CRYPNUM = 'M'
                        AND CRDATE  <= CURRENT_DATE
            AND CSGLNUM IN {'cpt1', 'cpt2'}

How can I do that.  I expected to find a standard function in the doc to 
do that ...

One way is to include the ' in with the delimiter, and start and end the 
string with ' as:

    AND CSGLNUM IN {'  || '\'' || array_to_string(Compte, '\', \'') || '\'' 
|| '}';

Regards,
George



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

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