Обсуждение: different results for large objects
I am getting correct results with following query i.e. binary objects as they are inserted.
select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename;
but when I created a function as follows, binary objects are not shown, rather a number represting the large object on the server is shown.
CREATE OR REPLACE FUNCTION software.uf_searchdocimages(integer, integer, integer)
RETURNS SETOF docimages AS
$BODY$
--select images for all object of specified type
select docimages.* from docimages where docimages.accobjecttype= $2 ORDER BY docimages.docimagename;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
The problem is arising for a field of type 'lo'. Why this function is not giving the actual large object?
I am using Windows XP sp3, PostgreSQL 8.3.6.
The table definitions is like below-
CREATE TABLE docimages
(
diid serial NOT NULL,
docimagename character varying(50),
docimage lo,
accobjectid integer,
accobjecttype integer,
CONSTRAINT pk_docimages PRIMARY KEY (diid)
)
WITH (OIDS=FALSE);
Please reply.
Thanks in adavnce.
CPKulkarni
select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename;
but when I created a function as follows, binary objects are not shown, rather a number represting the large object on the server is shown.
CREATE OR REPLACE FUNCTION software.uf_searchdocimages(integer, integer, integer)
RETURNS SETOF docimages AS
$BODY$
--select images for all object of specified type
select docimages.* from docimages where docimages.accobjecttype= $2 ORDER BY docimages.docimagename;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
The problem is arising for a field of type 'lo'. Why this function is not giving the actual large object?
I am using Windows XP sp3, PostgreSQL 8.3.6.
The table definitions is like below-
CREATE TABLE docimages
(
diid serial NOT NULL,
docimagename character varying(50),
docimage lo,
accobjectid integer,
accobjecttype integer,
CONSTRAINT pk_docimages PRIMARY KEY (diid)
)
WITH (OIDS=FALSE);
Please reply.
Thanks in adavnce.
CPKulkarni
c k <shreeseva.learning@gmail.com> writes: > I am getting correct results with following query i.e. binary objects as > they are inserted. > select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY > docimages.docimagename; > but when I created a function as follows, binary objects are not shown, There is no server-side behavior that would cause that query to show you a large object's contents instead of its identifying number. I suppose that you have some client-side code that does something magic when it sees a result column of type "lo", and that for some reason this code is not successfully recognizing the case where the column is coming out of a function. Since you've shown us neither the troublesome query nor the client-side code, it's hard to speculate further than that. regards, tom lane
Yes, the problem is with PGAdmin and ODBC connection to MS access.
The function (given is first mail) is giving same results i.e. only IDs not the actual data from PGAdmin and ODBC.
But for following query PGAdmin gives again only IDs, but MS Access through ODBC gives the actual binary data.
select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename;
So I think ODBc is making some magic here for showing large object data. If this is correct how can I retrieve the actual binary data from a function/single statement as above?
Thanks,
CPK
The function (given is first mail) is giving same results i.e. only IDs not the actual data from PGAdmin and ODBC.
But for following query PGAdmin gives again only IDs, but MS Access through ODBC gives the actual binary data.
select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename;
So I think ODBc is making some magic here for showing large object data. If this is correct how can I retrieve the actual binary data from a function/single statement as above?
Thanks,
CPK
On Tue, Mar 17, 2009 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
c k <shreeseva.learning@gmail.com> writes:There is no server-side behavior that would cause that query to show you
> I am getting correct results with following query i.e. binary objects as
> they are inserted.
> select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY
> docimages.docimagename;
> but when I created a function as follows, binary objects are not shown,
a large object's contents instead of its identifying number. I suppose
that you have some client-side code that does something magic when it
sees a result column of type "lo", and that for some reason this code
is not successfully recognizing the case where the column is coming out
of a function. Since you've shown us neither the troublesome query
nor the client-side code, it's hard to speculate further than that.
regards, tom lane