Re: how to convert a string array to a string. fct array_to_string seem to work only for INT array??
От | George Weaver |
---|---|
Тема | Re: how to convert a string array to a string. fct array_to_string seem to work only for INT array?? |
Дата | |
Msg-id | 003801c792ff$8ef78390$6400a8c0@Dell4500 обсуждение исходный текст |
Ответ на | how to convert a string array to a string. fct array_to_string seem to work only for INT array?? (David Gagnon <dgagnon@siunik.com>) |
Список | 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
В списке pgsql-general по дате отправления: