How to save PostgreSQL PL/pgSQL Output to a CSV File

Exporting PL/pgSQL Output to a CSV File in PostgreSQL

In PostgreSQL, you can save the output of a SELECT query or any result from a PL/pgSQL function to a CSV file. This can be done using the COPY command, which is efficient for exporting query results directly to a file. This guide covers how to save PL/pgSQL output to a CSV file step-by-step.

1. Using the COPY Command for Direct Export

The COPY command can export data directly to a CSV file. You can use it for any query that produces output, including within PL/pgSQL functions.

Syntax:

COPY (SELECT column1, column2 FROM table_name WHERE condition) 
TO '/path/to/your/file.csv' 
WITH (FORMAT CSV, HEADER);

• TO '/path/to/your/file.csv': Specifies the path and filename where you want to save the output.
• WITH (FORMAT CSV, HEADER): Exports in CSV format and includes a header row.

Example Code:

-- Export the result of a query to a CSV file
COPY (SELECT id, name, email FROM users WHERE active = true)  -- Select data to export
TO '/tmp/active_users.csv'        -- Define file path
WITH (FORMAT CSV, HEADER);    -- Specify CSV format with header

Explanation:

• COPY (SELECT id, name, email FROM users WHERE active = true): Executes a query on the users table to select only active users.
• TO '/tmp/active_users.csv': Saves the output in a file named active_users.csv located in the /tmp directory.
• WITH (FORMAT CSV, HEADER): Exports the result in CSV format and includes a header with column names.

2. Using psql to Export Query Results to CSV

You can run COPY commands directly within the psql command-line tool to export data to a CSV file without using a PL/pgSQL function.

Example Code:

# Run psql and export data to CSV
psql -U postgres -d your_database -c "\COPY (SELECT * FROM your_table) TO '/path/to/file.csv' CSV HEADER"

Explanation:

• psql -U postgres -d your_database: Connects to PostgreSQL as the specified user and database.
• \COPY (SELECT * FROM your_table) TO '/path/to/file.csv' CSV HEADER: Exports the results to a CSV file with headers.

3. Using PL/pgSQL Function to Generate CSV Data

You can also use a PL/pgSQL function to generate specific data, and then export the output with COPY.

Example Code:

CREATE OR REPLACE FUNCTION export_active_users_to_csv() RETURNS void AS $$
DECLARE
BEGIN
    -- Export query result to a CSV file
    PERFORM COPY (SELECT id, name FROM users WHERE active = true)  -- Fetch active users
    TO '/tmp/active_users.csv'                                     -- Define file path
    WITH (FORMAT CSV, HEADER);                                     -- Specify CSV format with header
END;
$$ LANGUAGE plpgsql;

Explanation:

• CREATE OR REPLACE FUNCTION export_active_users_to_csv(): Creates a function that exports active users to a CSV file.
• COPY (SELECT id, name FROM users WHERE active = true) TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER);: Executes a COPY command within the function, exporting only active users to a CSV file.

Important Notes

• File Path Permissions: Ensure PostgreSQL has permission to write to the specified file path. Absolute paths are recommended.
• Using psql with \COPY: If running the command as a regular user with psql, use \COPY instead of COPY to save files to the local system.
• Automated Exports: Schedule PL/pgSQL functions with cron or similar tools to automate regular exports.