src/bin/psql/large_obj.c

   1 /*
   2  * psql - the PostgreSQL interactive terminal
   3  *
   4  * Copyright (c) 2000-2011, PostgreSQL Global Development Group
   5  *
   6  * src/bin/psql/large_obj.c
   7  */
   8 #include "postgres_fe.h"
   9 #include "large_obj.h"
  10
  11
  12 #include "settings.h"
  13 #include "common.h"
  14
  15 static void
  16 print_lo_result(const char *fmt,...)
  17 __attribute__((format(printf, 1, 2)));
  18
  19 static void
  20 print_lo_result(const char *fmt,...)
  21 {
  22         va_list         ap;
  23
  24         if (!pset.quiet)
  25         {
  26                 if (pset.popt.topt.format == PRINT_HTML)
  27                         fputs("<p>", pset.queryFout);
  28
  29                 va_start(ap, fmt);
  30                 vfprintf(pset.queryFout, fmt, ap);
  31                 va_end(ap);
  32
  33                 if (pset.popt.topt.format == PRINT_HTML)
  34                         fputs("</p>\n", pset.queryFout);
  35                 else
  36                         fputs("\n", pset.queryFout);
  37         }
  38
  39         if (pset.logfile)
  40         {
  41                 va_start(ap, fmt);
  42                 vfprintf(pset.logfile, fmt, ap);
  43                 va_end(ap);
  44                 fputs("\n", pset.logfile);
  45         }
  46 }
  47
  48
  49 /*
  50  * Prepare to do a large-object operation.      We *must* be inside a transaction
  51  * block for all these operations, so start one if needed.
  52  *
  53  * Returns TRUE if okay, FALSE if failed.  *own_transaction is set to indicate
  54  * if we started our own transaction or not.
  55  */
  56 static bool
  57 start_lo_xact(const char *operation, bool *own_transaction)
  58 {
  59         PGTransactionStatusType tstatus;
  60         PGresult   *res;
  61
  62         *own_transaction = false;
  63
  64         if (!pset.db)
  65         {
  66                 psql_error("%s: not connected to a database\n", operation);
  67                 return false;
  68         }
  69
  70         tstatus = PQtransactionStatus(pset.db);
  71
  72         switch (tstatus)
  73         {
  74                 case PQTRANS_IDLE:
  75                         /* need to start our own xact */
  76                         if (!(res = PSQLexec("BEGIN", false)))
  77                                 return false;
  78                         PQclear(res);
  79                         *own_transaction = true;
  80                         break;
  81                 case PQTRANS_INTRANS:
  82                         /* use the existing xact */
  83                         break;
  84                 case PQTRANS_INERROR:
  85                         psql_error("%s: current transaction is aborted\n", operation);
  86                         return false;
  87                 default:
  88                         psql_error("%s: unknown transaction status\n", operation);
  89                         return false;
  90         }
  91
  92         return true;
  93 }
  94
  95 /*
  96  * Clean up after a successful LO operation
  97  */
  98 static bool
  99 finish_lo_xact(const char *operation, bool own_transaction)
 100 {
 101         PGresult   *res;
 102
 103         if (own_transaction && pset.autocommit)
 104         {
 105                 /* close out our own xact */
 106                 if (!(res = PSQLexec("COMMIT", false)))
 107                 {
 108                         res = PSQLexec("ROLLBACK", false);
 109                         PQclear(res);
 110                         return false;
 111                 }
 112                 PQclear(res);
 113         }
 114
 115         return true;
 116 }
 117
 118 /*
 119  * Clean up after a failed LO operation
 120  */
 121 static bool
 122 fail_lo_xact(const char *operation, bool own_transaction)
 123 {
 124         PGresult   *res;
 125
 126         if (own_transaction && pset.autocommit)
 127         {
 128                 /* close out our own xact */
 129                 res = PSQLexec("ROLLBACK", false);
 130                 PQclear(res);
 131         }
 132
 133         return false;                           /* always */
 134 }
 135
 136
 137 /*
 138  * do_lo_export()
 139  *
 140  * Write a large object to a file
 141  */
 142 bool
 143 do_lo_export(const char *loid_arg, const char *filename_arg)
 144 {
 145         int                     status;
 146         bool            own_transaction;
 147
 148         if (!start_lo_xact("\\lo_export", &own_transaction))
 149                 return false;
 150
 151         SetCancelConn();
 152         status = lo_export(pset.db, atooid(loid_arg), filename_arg);
 153         ResetCancelConn();
 154
 155         /* of course this status is documented nowhere :( */
 156         if (status != 1)
 157         {
 158                 fputs(PQerrorMessage(pset.db), stderr);
 159                 return fail_lo_xact("\\lo_export", own_transaction);
 160         }
 161
 162         if (!finish_lo_xact("\\lo_export", own_transaction))
 163                 return false;
 164
 165         print_lo_result("lo_export");
 166
 167         return true;
 168 }
 169
 170
 171 /*
 172  * do_lo_import()
 173  *
 174  * Copy large object from file to database
 175  */
 176 bool
 177 do_lo_import(const char *filename_arg, const char *comment_arg)
 178 {
 179         PGresult   *res;
 180         Oid                     loid;
 181         char            oidbuf[32];
 182         bool            own_transaction;
 183
 184         if (!start_lo_xact("\\lo_import", &own_transaction))
 185                 return false;
 186
 187         SetCancelConn();
 188         loid = lo_import(pset.db, filename_arg);
 189         ResetCancelConn();
 190
 191         if (loid == InvalidOid)
 192         {
 193                 fputs(PQerrorMessage(pset.db), stderr);
 194                 return fail_lo_xact("\\lo_import", own_transaction);
 195         }
 196
 197         /* insert description if given */
 198         if (comment_arg)
 199         {
 200                 char       *cmdbuf;
 201                 char       *bufptr;
 202                 size_t          slen = strlen(comment_arg);
 203
 204                 cmdbuf = malloc(slen * 2 + 256);
 205                 if (!cmdbuf)
 206                         return fail_lo_xact("\\lo_import", own_transaction);
 207                 sprintf(cmdbuf, "COMMENT ON LARGE OBJECT %u IS '", loid);
 208                 bufptr = cmdbuf + strlen(cmdbuf);
 209                 bufptr += PQescapeStringConn(pset.db, bufptr, comment_arg, slen, NULL);
 210                 strcpy(bufptr, "'");
 211
 212                 if (!(res = PSQLexec(cmdbuf, false)))
 213                 {
 214                         free(cmdbuf);
 215                         return fail_lo_xact("\\lo_import", own_transaction);
 216                 }
 217
 218                 PQclear(res);
 219                 free(cmdbuf);
 220         }
 221
 222         if (!finish_lo_xact("\\lo_import", own_transaction))
 223                 return false;
 224
 225         print_lo_result("lo_import %u", loid);
 226
 227         sprintf(oidbuf, "%u", loid);
 228         SetVariable(pset.vars, "LASTOID", oidbuf);
 229
 230         return true;
 231 }
 232
 233
 234 /*
 235  * do_lo_unlink()
 236  *
 237  * removes a large object out of the database
 238  */
 239 bool
 240 do_lo_unlink(const char *loid_arg)
 241 {
 242         int                     status;
 243         Oid                     loid = atooid(loid_arg);
 244         bool            own_transaction;
 245
 246         if (!start_lo_xact("\\lo_unlink", &own_transaction))
 247                 return false;
 248
 249         SetCancelConn();
 250         status = lo_unlink(pset.db, loid);
 251         ResetCancelConn();
 252
 253         if (status == -1)
 254         {
 255                 fputs(PQerrorMessage(pset.db), stderr);
 256                 return fail_lo_xact("\\lo_unlink", own_transaction);
 257         }
 258
 259         if (!finish_lo_xact("\\lo_unlink", own_transaction))
 260                 return false;
 261
 262         print_lo_result("lo_unlink %u", loid);
 263
 264         return true;
 265 }
 266
 267
 268
 269 /*
 270  * do_lo_list()
 271  *
 272  * Show all large objects in database with comments
 273  */
 274 bool
 275 do_lo_list(void)
 276 {
 277         PGresult   *res;
 278         char            buf[1024];
 279         printQueryOpt myopt = pset.popt;
 280
 281         if (pset.sversion >= 90000)
 282         {
 283                 snprintf(buf, sizeof(buf),
 284                                  "SELECT oid as \"%s\",\n"
 285                                  "  pg_catalog.pg_get_userbyid(lomowner) as \"%s\",\n"
 286                         "  pg_catalog.obj_description(oid, 'pg_largeobject') as \"%s\"\n"
 287                                  "  FROM pg_catalog.pg_largeobject_metadata "
 288                                  "  ORDER BY oid",
 289                                  gettext_noop("ID"),
 290                                  gettext_noop("Owner"),
 291                                  gettext_noop("Description"));
 292         }
 293         else
 294         {
 295                 snprintf(buf, sizeof(buf),
 296                                  "SELECT loid as \"%s\",\n"
 297                    "  pg_catalog.obj_description(loid, 'pg_largeobject') as \"%s\"\n"
 298                          "FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) x\n"
 299                                  "ORDER BY 1",
 300                                  gettext_noop("ID"),
 301                                  gettext_noop("Description"));
 302         }
 303
 304         res = PSQLexec(buf, false);
 305         if (!res)
 306                 return false;
 307
 308         myopt.topt.tuples_only = false;
 309         myopt.nullPrint = NULL;
 310         myopt.title = _("Large objects");
 311         myopt.translate_header = true;
 312
 313         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 314
 315         PQclear(res);
 316         return true;
 317 }