Re: COPY value TO STDOUT

Hi

2017-01-13 16:45 GMT+01:00 Denisa Cirstescu <denisa(dot)cirstescu(at)asentinel(dot)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