From: | George Weaver <gweaver(at)shaw(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to convert a string array to a string. fct array_to_string seem to work only for INT array?? |
Date: | 2007-05-10 12:34:29 |
Message-ID: | 003801c792ff$8ef78390$6400a8c0@Dell4500 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sim Zacks | 2007-05-10 12:50:21 | Re: Invoke trigger after commit |
Previous Message | Leif B. Kristensen | 2007-05-10 12:07:45 | Re: Views- Advantages and Disadvantages |