Problems with array type?

Question

I’m having trouble formulating a chart.

I have basically 2 fields: categorias and dados. Within categorias, the information must be so: ['categoria 1', 'categoria 2', 'etc'] in quotes and separated by comma. Already inside data, the information needs to come like this: [dado 1, dado 2, dado 3] quote-free.

I need to get both information (categorias and dados) of a select in a Postgresql database, but only succeed in the field dados, since it never comes separated with quotes. How can I format to include quotes?

This is an example of code for data:

$query="SELECT colunaa from minha tabela";
$output=pg_query($conn,$query);
    ?>
    [<?php while ($result = pg_fetch_array($output)) {?>
                      <?php echo $result["colunaa"]?>,

                    <?php } ?>]

Answer 1

You can concatenate the quotes:

SELECT CONCAT('''', colunaa, '''') from minhatabela

I hope I’ve helped.

Answer 2

In PostgreSQL you can do something like:

CREATE TABLE tb_cores
(
    id INTEGER NOT NULL PRIMARY KEY,
    nome TEXT NOT NULL
);

INSERT INTO tb_cores ( id, nome ) VALUES ( 1, 'PRETO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 2, 'BRANCO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 3, 'VERMELHO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 4, 'VERDE' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 5, 'AZUL' );

1) COM Aggregation (separated by commas):

SELECT string_agg( nome, ',' ) FROM tb_cores;

Exit:

|                       string_agg |
|----------------------------------|
| PRETO,BRANCO,VERMELHO,VERDE,AZUL |

2) COM Aggregation (with quotation marks and separated by commas):

SELECT CONCAT( $$'$$, string_agg( nome, $$','$$ ), $$'$$ ) FROM tb_cores;

Exit:

|                                     concat |
|--------------------------------------------|
| 'PRETO','BRANCO','VERMELHO','VERDE','AZUL' |

3) NO aggregation, only quotation marks:

SELECT CONCAT( $$'$$, nome, $$'$$ ) FROM tb_cores;

Exit:

|     concat |
|------------|
|    'PRETO' |
|   'BRANCO' |
| 'VERMELHO' |
|    'VERDE' |
|     'AZUL' |

Sqlfiddle