Re: what are correct steps to programmatically write/read largeobjects to/from a data base table? - Mailing list pgsql-novice
From | Laurenz Albe |
---|---|
Subject | Re: what are correct steps to programmatically write/read largeobjects to/from a data base table? |
Date | |
Msg-id | 1523515389.2410.12.camel@cybertec.at Whole thread Raw |
In response to | what are correct steps to programmatically write/read large objects to/from a data base table? ("Ian Bell" <ib@ianbellsoftware.com>) |
Responses |
RE: what are correct steps to programmatically write/read large objects to/from a data base table?
|
List | pgsql-novice |
Ian Bell wrote: > I have just started working with PostgreSQL (v10.3) running under Window 10. > Also I am new to SQL in general. I am writing test code to see how to write/read > large objects and have a question about the order/number of steps when > executing code written in C/C++ and C#. > > I want to write and read binary data as large objects. > > 1) In PSQL, I do this using the following commands: > > create database mydb; > \c mydb; > create table test( name text, mylargevariable oid); > insert into test( name, mylargevariable) values( ‘n1’, lo_import(‘f:/temp/test.txt’) ); > select convert_from( lo_get(test.large), ‘UTF8’) from test; > > The point of this example is that the large object id value (‘OID’) is stored in the > ‘mylargevariable’ field of the table ‘test’. My question is about how to programmatically > do this using code written in C/C++ and C#. I address this in my next point. > > 2) The PostgreSQL sample code illustrates how to write/read a large object in C/C++ > while the NPGSQL sample code illustrates how to write/read large objects in C#. > I am able to successfully compile and run both sets of sample code. > Here are my questions: > > a. My understanding is these examples write the binary data as a large object to the > ‘pg_largeobject’ table. Would you please confirm this is correct? > > b. If I want to be able to retrieve the binary data associated with a large object stored > in the ‘pg_largeobject’ table at a later time (i.e. start a new process or run a > new application) then I will have to persist/save/write the OID’s referencing the > large object to a table when the large object is first created. So the pseudo code > for both C/C++ and C# programs would be as follow: > > i. Create my database and create my own table which will store large object OIDs > ii. Create a large object OID (e.g. call the function ‘lo_creat’ in the C libpg library) > iii. Write the binary data to this large object (e.g. call the function ‘lo_write’ in the > C libpg library) > iv. Write the large object OID generated in step ii to a PostgreSQL table that I created in step i > > Would you please confirm these steps and their order are correct? The reason for asking > this question is I want to confirm that i) while non-large objects require only one write > to the data base ii) in contrast, large-objects require two writes (i.e. the first to write > the large object, the second to insert/write the large object OID to a table field). > I actually have written test code that does just this and it seems to be working. > However, given my lack of experience with PostgreSQL and SQL in general, I would like > confirmation the writing/reading binary data large object is this simple and that I have not > missed something important. That is all correct, and I think you have understood large objects well. Have a look at the "lo" contrib: https://www.postgresql.org/docs/current/static/lo.html Some of its functionality might help you to manage the large objects. I'd like to remark that adding large "bytea" values to a table also affects two tables: The table itself and the "TOAST table" where the bytea will be stored out of line. But of course it is only one client-server round trip. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
pgsql-novice by date: