COPY value TO STDOUT

From: Denisa Cirstescu <denisa(dot)cirstescu(at)asentinel(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: COPY value TO STDOUT
Date: 2017-01-13 15:45:39
Message-ID: BY2PR05MB2309EABA3DEFA0143F50F0D593780@BY2PR05MB2309.namprd05.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am not sure if this is the correct mailing list or if this is how you submit a question, but I am going to give it a try.

I want to COPY a value to STDOUT from PL/pgSQL language.

I saw that the STDOUT is not accessible from PL/pgSQL, but it is from SQL.
This is why I am trying to create an auxiliary function declared as language SQL and call that function from my PL/pgSQL code.

This is an example of PL/pgSQLcode:

DO
$do$
BEGIN
perform printToStdout('12');
perform printToStdout('34');
perform printToStdout('56');
END
$do$

And this is the definition of the auxiliary function:

create or replace function printToStdout(abc text) returns void as $$
copy (SELECT abc) to stdout;
$$ language sql;

However, this is not working, because COPY doesn't know how to use the value of my variable named abc and it returns the error:
ERROR: column "abc" does not exist
If I use a constant, instead of the abc variable everything works fine; the constant is printed to STDOUT.

Is there a way to achieve this without using an auxiliary table?
The below code does the job, but is not ok for me because of the auxiliary table that might cause performance problems in a concurrent environment with a lot of requests:

create table if not exists printToStdoutTable(abc text);

create or replace function printToStdout(abc text) returns void as $$
delete from printToStdoutTable;
insert into printToStdoutTable values(abc);
copy (SELECT * from printToStdoutTable) to stdout;
$$ language sql;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message kbrannen 2017-01-13 18:03:45 Re: efficiently migrating 'old' data from one table to another
Previous Message Jonathan Vanasco 2017-01-13 15:42:20 Re: efficiently migrating 'old' data from one table to another