Search code examples
shpsqlgreenplum

Store the query results with multiple rows into csv and taking table name from user

I am trying to store a Greenplum query result into a csv but it is storing only one row. I want to store multiple rows. Can anyone help with this. I tried the below already but it is storing only one row.

r= `psql -A -t -q  -h $hostname -U $username  -d $dbname <<-THE_END
COPY(select * From ${gschema}.${gtable} order by ${key} limit 3) TO STDOUT with NULL as ' ' 
THE_END` > /home/gp.csv

I also tried below which is storing the result but I am unable to pass table name as parameter in the below query. For standard table names the output is as desired.

psql -h $hostname -U $username  -d $dbname -c "COPY (select * from table_name order by key limit 3) TO STDOUT "> /home/gp.csv

Can anyone help me with this please. please note that I am trying to embed the above in a shell script

Solution

  • Try this:

    psql -A -t -q -h $hostname -U $username -d $dbname -c "select * from $gschema.$gtable order by $key limit 3" > /home/gp.csv

    You could also put the SQL in a file:

    example.sql

    select * from :gschema.:gtable order by :key limit 3

    And then to use the sql file:

    psql -A -t -q -h $hostname -U $username -d $dbname -f example.sql -v gschema=public -v gtable=customer -v key=id > /home/gp.csv