PSQL procedures need to be able to RETURN a blob as a blob_id (GDS_QUAD)
------------------------------------------------------------------------
Key: CORE-5811
URL: http://tracker.firebirdsql.org/browse/CORE-5811
Project: Firebird Core
Issue Type: New Feature
Components: Engine
Environment: PSQL needs to be able to read and pass blob_ids like a
normal SQL statement
Reporter: Ray Holme
There needs to be a return type for blobs that acts like a standard SQL
statement would
- simplest example of what is needed (FB_QUAD or ISC_QUAD or GDS_QUAD or ????)
----
create or alter procedure blob_me returns (blob_id ???????) as
begin
for select some_blob from some_table into :blob_id do suspend; end
end^
----
The example above is absurd but is the simplest illustration I can come up with.
The real need is for a procedure to do an elegant tag (performance issues) sort
where one or more fields returned are blobs.
Note that having a "BLOB TYPE N" is also nice and is for working with the
contents of the blob.
In this case the procedure does not want to be working with the contents but
simply to pass the handle down to the program calling
for it to determine what should be done with the blob. Returing the blob
contents to a multi row select with an order by clause
would probably cause truly big performance issues.
The below procedure example (hoaky names, sorry) illustrates the real
underlying need for this
consider all three tables large and the resulting set would be many thousand
rows
--- the need to tag sort this data for perormance should be self evident.
-----+++++
create or alter procedure tag_sort
returns (akey integer. bkey integer. ckey integer,
aval integer, ablob_id ???????, bval varchar(400), cval
varchar(2000), cblob_id ??????) as
begin
for select a.akey, b.bkey, c.ckey
from some_tablea a left outer join some_tableb b on (a.funky = b.funky)
inner join some_tablec c on (b.what = c.what)
where a.something > 3 and b.otherthing in (4,5,6)
order by a.somename desc, b.somevalue, b.othervalue, c.strangevalue
into :akey, :bkey, :ckey do
begin
select aval, ablob_id from some_tablea where akey = :akey into :aval,
:ablob_id;
select bval from some_tableb where bkey = :bkey into
:bval;
select cval, cblob_id from some_tablec where ckey = :ckey into :cval,
:cblob_id;
suspend;
end
end^
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
