Re: Help : Microsoft SQL Server equivalents in PostGreSQL

Benedict Faria wrote:
> I need to use a postgreSQL equivalent for "Updatetext" and
> "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL
> equivalent is?

Hi Benedict,

I don't see an exact equivalent to MS SQL Server's UpdateText (and
ReadText) commands in pgsql or any other PostgreSQL procedural
language. The closest thing would be to use array syntax (e.g. UPDATE
table SET field[start:end] = {val1, val2, val3}) but npgsql doesn't
seem to support passing array datatypes yet. Also it isn't clear from
the PostgreSQL docs whether the array slice syntax can be used on the
bytea type. If not, I think you'd need to use an array of smallints
(2-byte ints) which might hurt performance and storage.

I think your best bet is to use a large object. Npgsql has support for
writing and reading large objects in chunks. The primary difference
from the UpdateText SQL function that MS SQL Server provides is that
you will pass the chunks to Npgsql's LargeObject.Write() method instead
of passing the chunks to an SQL command or stored proc. Also, instead
of putting the data directly in the row, you'll put it in a large
object and then put the OID of the large object in the row.

For more info about Npgsql's large object support see:

http://npgsql.projects.postgresql.org/docs/manual/UserManual.htm

http://npgsql.projects.postgresql.org/docs/api/NpgsqlTypes.LargeObject.html

Hope that helps,

--Dean