I/O File Operations in Postgresql

Question

I am brand new to PostgreSQL and I need someone to point me in the right direction on how to write the results of a function to a text file. Is it possible to do this within the PostgreSQL PL/pgSQL language? I have done this before in Oracle using the UTL_FILE commands and I was hoping that PostgreSQL had similar functionality. Thanks in advance for any help that you can give.

Kindly provide some examples of file operations.

        CREATE OR REPLACE FUNCTION NP_AC015_FETCH.proc_log (P_MSG text, P_MODE integer default 1) RETURNS VOID AS $body$
    DECLARE

        V_F_IS_OPEN boolean;      --IF LOG FILE IS ALREADY OPEN THIS IS SET TO TRUE
        V_LOG_MSG varchar(32767); --LOG FILE NAME
        V_LOG_DIR varchar(30) := 'ND_GANJIS_LOG_DIR'; --LOG DIRECOTY

        vTemp UTL_FILE.FILE_TYPE;
    BEGIN
        select get_var('GM_LOG_FILE') INTO vTemp;

        V_F_IS_OPEN := utl_file.is_open(vTemp);

        if not V_F_IS_OPEN then

          -- Log File Open
          -- 32767 IS THE MAXIMUM NUMBER OF CHARACTERS PER LINE, INCLUDING THE NEWLINE CHARACTER, FOR THIS FILE.
          vTemp := UTL_FILE.FOPEN(V_LOG_DIR, 'NIA_PLSQL_'||to_char(clock_timestamp(), 'yyyymmdd')||'.log', 'A', 32767);

        end if;

        -- LOG MSG TO BE WRITTEN TO THE LOG FILE
        V_LOG_MSG := TO_CHAR(CURRENT_TIMESTAMP, 'yyyy/mm/dd hh24:mi:ss:ff3') ||' '|| P_MSG;

        --Output messages to a file
        UTL_FILE.PUT_LINE(vTemp, V_LOG_MSG);

    --Closing Log File
    if P_MODE = current_setting('NP_AC015_FETCH.PV_LOG_CLOSE_MODE')::pls_integer and utl_file.is_open(vTemp) then

          utl_file.fclose(vTemp);

    end if;

    --HERE THE EXCEPTION PART IS NOT INCLUDED,
    --Reason： PROGRAM WILL GO ON INFINITE LOOP IF SOME ERROR OCCURS HERE, BECAUSE, EACH EXCEPTION WRITES INTO
    --LOG FILE, USING THIS PROCEDURE.
   exception

    when others then

      RAISE EXCEPTION '%', dbms_utility.format_error_backtrace||chr(10)||dbms_utility.format_error_stack||chr(10)||dbms_utility.format_call_stack, true;

    END;
$body$
LANGUAGE PLPGSQL;

You might want to look up Foreign Data wrappers. There is nothing in PostgreSQL similar to UTL_FILE. https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_Wrappers — Jayadevan, Apr 20 '17 at 05:30
@Jayadevan...Thanks for your reply. Could you please post sample example on how to export data to a text file using FDW ? — User0123, Apr 20 '17 at 06:16
Looks like FDW is read-only. Does this SO answer meet your needs? http://stackoverflow.com/questions/4897085/dynamically-generated-table-name-in-postgresql-copy-command I tested a slightly modified version of the code and it worked. Here is the code create or replace function dynamiccopy(tablename text, outname text) returns void as $body$ begin execute format('copy (select * from %s) to ''/tmp/%s.csv''', tablename, outname); end $body$ language plpgsql; — Jayadevan, Apr 20 '17 at 07:24

score 0 · Answer 1 · answered Apr 20 '17 at 10:10

You can install the adminpack contrib module and use the function pg_file_write(filename text, data text, append boolean).

Note that this function is restricted to superusers, but you can create a SECURITY DEFINER function owned by a superuser that provides the necessary functionality to the users you choose.

I/O File Operations in Postgresql

1 Answers1