0

When I populate a table from the psql terminal:

COPY schema.table (
    column_1
    , column_2
    , column_3
)
FROM 'file.csv'
WITH (OPTIONS);

the message:

COPY x

is displayed in the terminal if successful. But when I embed the COPY statement in a function e.g. populate_table with:

EXECUTE format(
    'COPY schema.%I (
        column1
        , column2
        , column3
    )
FROM ''file.txt''
WITH (FORMAT);'
, table_name);

and run the function the terminal output is different in that I see:

 populate_table 
-----------------------

(1 row)

Similarly, if I UPDATE with a function I lose the message:

UPDATED x x

Is it possible to print these messages to the terminal and capture them when working with functions?

0

I found an answer to this question here: https://stackoverflow.com/questions/16610449/get-the-count-of-rows-from-a-copy-command. The answer points to a new feature of PL/pgSQL whereby

COPY executed in a PL/pgSQL function now updates the value retrieved by GET DIAGNOSTICS x = ROW_COUNT.

Using this information I've tried it as:

CREATE OR REPLACE FUNCTION populate_table (
    table_name CHARACTER VARYING
)
RETURNS INTEGER AS
$$
DECLARE    
rows_copied INTEGER;
BEGIN
    EXECUTE format(
        'COPY schema.%I (
            column1
            , column2
            , column3 )
        FROM ''file.txt''
        WITH (FORMAT);'
    , table_name);

    GET DIAGNOSTICS rows_copied := ROW_COUNT;
    RETURN rows_copied;
END
$$ LANGUAGE plpgsql;

and the terminal message now is:

populate_table 
--------------
            x
(1 row)

If I further tweak the psql settings with:

\t [on|off]            show only rows (currently on)
\a                     toggle between unaligned and aligned output mode

the terminal output is:

x

which is exactly what I wanted.

  • @ErwinBrandstetter, it was your question on SO and the answer to it that helped me out here so this question is really a duplicate. I am not sure what the protocol here is but I am happy to delete this question from here, as it's already been answered over on SO. – dw8547 Sep 22 '17 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.