In PostgreSQL, where does plpython(3)u output from `print` go?

Question

When I create the following function in PostgreSQL:

create function log( value variadic text[] )
  returns void
  language plpython3u
  as $$
    print( ' '.join( value ) + '\n' )
    $$;

do $$ begin perform log( ( 42 + 108 )::text ); end; $$;

the ouput does not appear in the terminal (using psql -f ...). Where does it go instead?

Also, isn't there any simple way (maybe an extension) that gives me an easy-to-use output to stdout / stderr? I do not want to use select since that surrounds all output with table decorations, which you can turn off, but only using psql tricks that do not work in functions. Likewise, I do not want to use \echo, as that won't work within function definitions.

Edit I know about plpy.notice(), but that function surrounds its output with a whole lot of clutter, too.

BTW my solution right now is to write to a file:

create function log( value variadic text[] )
  returns void
  language plpython3u
  as $$
    with open( '/tmp/psql-output', 'a' ) as o:
      o.write( ' '.join( value ) + '\n' )
    $$;

and have a tail running in the background (that uses ANSI colors, yay!):

tail -f /tmp/psql-output | sed 's/^.*$/\x1b[38;05;214m\0\x1b[0m/g' &

but the disadvantage is that I need external code to set up this thing.

score 7 · Answer 1 · answered Nov 19 '19 at 09:36

You can use the function of plpy to print the message.

plpy.debug(msg, **kwargs)
plpy.log(msg, **kwargs)
plpy.info(msg, **kwargs)
plpy.notice(msg, **kwargs)
plpy.warning(msg, **kwargs)

plpy.log() is always output if log_destination is "stderr", and others functions are according to log_min_messages value(warning is default).

score 0 · Answer 2 · answered Sep 04 '21 at 18:53

The other answer put into practice with an easy example:

create or replace function pddesc(x numeric[])
returns table(count float, mean float, std float, min float)
as $$
    import pandas as pd
    import numpy as np
    data=pd.Series(x)

    count=data.describe()[0]
    mean=data.describe()[1]
    std=data.describe()[2]
    min=data.describe()[3]

    ## print an INFO of the output:
    plpy.debug('debug:', np.array([count, mean, std, min]))
    plpy.log(np.array([count, mean, std, min]))
    plpy.info(np.array([count, mean, std, min]))
    plpy.info('test')
    plpy.notice(np.array([count, mean, std, min]))
    plpy.notice(np.array([count, mean, std, min]).reshape(1,-1))
    plpy.warning(np.array([count, mean, std, min]))

    return np.array([count, mean, std, min]).reshape(1,-1)
    ## or with the same result:
    # return np.hstack((count, mean, std, min)).reshape(1,-1)

$$ language plpython3u;

Output:

postgres=# SELECT * FROM pddesc(ARRAY[1,2,3]);
INFO:  [3 3 Decimal('1') 1]
INFO:  test
NOTICE:  [3 3 Decimal('1') 1]
NOTICE:  [[3 3 Decimal('1') 1]]
WARNING:  [3 3 Decimal('1') 1]
 count | mean | std | min
-------+------+-----+-----
     3 |    3 |   1 |   1
(1 row)

We see that info and notice can be used like print. debug and log do not show up.

In PostgreSQL, where does plpython(3)u output from `print` go?

2 Answers2