2

I would like to do the equivalent of c or php fopen() and fwrite(). I am not trying to dump a table to disk. I am trying to do some debug logging during development.

Tim Duncklee
  • 1,420
  • 1
  • 23
  • 35
  • If you need some statistics about a query you should take a look at :https://www.postgresql.org/docs/9.1/static/sql-explain.html – Simon Ludwig Oct 02 '16 at 00:30
  • Actually I'm trying to trace a logic flow issue. I have many of this type of functions to write and it would be nice if I could have some debugging fwrite() statements so I can see a log of what happens in my code. – Tim Duncklee Oct 02 '16 at 00:35
  • 1
    For generic "write to a file" problem (not only log-file), see https://stackoverflow.com/q/48327289/287948 – Peter Krauss Jan 27 '18 at 01:21

3 Answers3

2

You can use plpythonu f.open(), f.write(), f.close() within a postgres function to write to a file.

Language extension would need to be installed.,

https://www.postgresql.org/docs/8.3/static/plpython.html

Working example from the mailing list. https://www.postgresql.org/message-id/flat/20041106125209.55697.qmail%40web51806.mail.yahoo.com#20041106125209.55697.qmail@web51806.mail.yahoo.com

for example plpythonu

CREATE FUNCTION makefile(text) RETURNS text AS '\n
o=open("/path/to/file")
o.write(args[0])
o.close()
return "ok"
' LANGUAgE plpythonu;

Regards Tino

VynlJunkie
  • 1,953
  • 22
  • 26
1

You can RAISE NOTICE or DEBUG messages in a plpgsql function or a DO statement which are written to the DB log file.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

PostgreSQL 12 + Python 3

    CREATE OR REPLACE FUNCTION "public"."makefile"("path" text, "content" text)
      RETURNS "pg_catalog"."text" AS $BODY$
        import os
        import stat
        o=open(path,'w')
        o.write(content)

        # stat.S_IRUSR Owner has read permission.
        # stat.S_IWUSR Owner has write permission.
        # stat.S_IRGRP Group has read permission.
        # stat.S_IWGRP Group has write permission.
        # stat.S_IROTH Others have read permission.
        os.chmod(path, stat.S_IRUSR | stat.S_IWUSR | stat.S_IRGRP | stat.S_IWGRP | stat.S_IROTH)
        o.close()
        return 'ok'
    $BODY$
      LANGUAGE plpython3u VOLATILE
Pavel Dmitrenko
  • 310
  • 2
  • 7