Sfrgttk

What is Tony Stark injecting into himself in Iron Man 3?

(Codewars) Linked Lists - Remove Duplicates

Help understanding 1986 schematic for Rohde & Schwarz cryptographic key generator

Is this Paypal Github SDK reference really a dangerous site?

How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?

Which situations would cause a company to ground or recall a aircraft series?

What will be the sign of work done?

From an axiomatic set theoric approach why can we take uncountable unions?

What is the generally accepted pronunciation of “topoi”?

Source permutation

Which classes are needed to have access to every spell in the PHB?

Is it possible that a question has only two answers?

How to design an organic heat-shield?

Power Strip for Europe

What can I do if someone tampers with my SSH public key?

Gaining more land

Why is there an extra space when I type "ls" in the Desktop directory?

Is a piano played in the same way as a harmonium?

Plausibility of Mushroom Buildings

Rationale to prefer local variables over instance variables?

After `ssh` without `-X` to a machine, is it possible to change `$DISPLAY` to make it work like `ssh -X`?

Vocabulary for giving just numbers, not a full answer

Signed and unsigned numbers

Getting the || sign while using Kurier

Capture COPY & UPDATE command tag from an EXECUTE statement in a PL/pgSQL function?

CTE works as expected, but not when wrapped into a functionPostgreSQL and storing configsCan we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?PL/pgSQL issues when function used twice (caching problem ?)PL/pgsql: use cursor to update next record based on a condition from current recordUPDATE with LIMIT / OFFSET in PL/pgSQL functionExecute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql functionPostgres: query on huge (11gb ) index does not returnFire a trigger function when checks changeReturn both one value and one column from pl/pgsql function

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?

plpgsql postgresql-9.6

share|improve this question

edited Sep 22 '17 at 14:18

dw8547

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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?

plpgsql postgresql-9.6

share|improve this question

edited Sep 22 '17 at 14:18

dw8547

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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?

plpgsql postgresql-9.6

share|improve this question

edited Sep 22 '17 at 14:18

dw8547

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

COPY schema.table (

    column_1

    , column_2

    , column_3

)

FROM 'file.csv'

WITH (OPTIONS);

COPY x

EXECUTE format(

    'COPY schema.%I (

        column1

        , column2

        , column3

    )

FROM ''file.txt''

WITH (FORMAT);'

, table_name);

 populate_table 

-----------------------



(1 row)

UPDATED x x

share|improve this question

edited Sep 22 '17 at 14:18

dw8547

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

share|improve this question

edited Sep 22 '17 at 14:18

dw8547

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

asked Sep 20 '17 at 14:28

dw8547dw8547

3271313

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f186435%2fcapture-copy-update-command-tag-from-an-execute-statement-in-a-pl-pgsql-functi%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

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;

populate_table 

--------------

            x

(1 row)

t [on|off]            show only rows (currently on)

a                     toggle between unaligned and aligned output mode

x

share|improve this answer

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313

answered Sep 22 '17 at 14:56

dw8547dw8547

3271313