COPY value TO STDOUT

Hi

2017-01-13 16:45 GMT+01:00 Denisa Cirstescu <denisa.cirstescu@asentinel.com>
:

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;*

You cannot do it in plain text language.

The identifier of column or table have not be a variable ever.

You can use dynamic SQL in PLpgSQL - where SQL command is created in
run-time and there you can do what you want.

Use plpgsql and EXECUTE statement
https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards

Pavel Stehule

Denisa Cirstescu <denisa.cirstescu@asentinel.com> writes:

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

You can't. Maybe RAISE NOTICE would serve the purpose?

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.

Oh, that's an oversight --- this case can't work either, but the SQL
function code fails to prevent it. The COPY runs, but it completely
breaks the wire protocol, leading to weird errors on the client side,
eg

regression=# create or replace function printToStdout(abc text) returns void as $$
copy (SELECT 42) to stdout;
$$ language sql;
CREATE FUNCTION
regression=# select printToStdout('z');
42
server sent data ("D" message) without prior row description ("T" message)
regression=#

What this should produce is an error similar to the one you get in
plpgsql. COPY to stdout/from stdin can't be executed from inside an
already-running query, because the wire protocol can't support
nesting those operations.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general