Return a "results set", use a temporary table or use a table?

You can write SQL functions that return SETOF. e.g.
CREATE OR REPLACE FUNCTION f_get_range(integer, integer) RETURNS SETOF
NUMERIC
AS 'select test_id from test2 where test_id between $1 and $2;'
LANGUAGE SQL;

Unfortunately I don't think you can currently return sets in plpgsql
functions. Two practical alternatives are:
1. Get your resulset concatenated into a single variable
2. Insert your resultset into a table and then select it out

I think you already know who to do 2, so here's how you could do 1:
create table tst (
ID NUMERIC,
STR VARCHAR(255)
);

insert into tst (ID, STR) values (1, 'this');
insert into tst (ID, STR) values (2, 'will');
insert into tst (ID, STR) values (3, 'get');
insert into tst (ID, STR) values (4, 'displayed');
insert into tst (ID, STR) values (5, 'but');
insert into tst (ID, STR) values (6, 'this');
insert into tst (ID, STR) values (7, 'will');
insert into tst (ID, STR) values (8, 'not');

CREATE OR REPLACE FUNCTION num2text(NUMERIC) RETURNS text AS'
DECLARE
-- num2text is just a function to change values of type numeric into type
text
val NUMERIC;
result text;
sgn text;
fmtstr text;
scalefactor NUMERIC;
BEGIN
if $1 is null then
result := NULL;
else
-- get the sign
if $1 < 0 then
sgn := \'-\';
else
sgn := \'\';
end if;

-- get the integer part
val := abs($1);
scalefactor := 1000000;

if val >= scalefactor then
result := num2text(trunc(val/scalefactor));
val := val - trunc(val/scalefactor)*scalefactor;
fmtstr := \'000009\';
else
result := \'\';
fmtstr := \'999999\';
end if;

result := sgn||result||trim(to_char(val, fmtstr));

-- Now add the decimal bit
if val - trunc(val) > 0 then
result := result ||\'.\';
val := val - trunc(val);
end if;
while val - trunc(val) > 0 loop
val := val * 10;
result := result||trim(to_char(trunc(val), \'0\'));
val := val - trunc(val);
end loop;

end if;
RETURN result;
END;'
LANGUAGE 'plpgsql';

create or replace function f_get_id(NUMERIC, NUMERIC) RETURNS TEXT AS'
DECLARE
dpsql text;
dprec RECORD;
result text;
BEGIN
result := \',\';
dpsql := \'select num2text(id) AS idstr from tst where id between
\'||num2text($1)||\' and \'||num2text($2)||\';\';
for dprec in execute dpsql loop
result := result || dprec.idstr || \',\';
end loop;
RETURN result;
END;'
LANGUAGE 'plpgsql';

select f_get_id(1,4);

select *
from tst
where f_get_id(1, 4) like '%,'||num2text(id)||',%'
order by id;

The first query will return ',1,2,3,4,'. The second will return:
id | str
----+-----------
1 | this
2 | will
3 | get
4 | displayed

Now, this example is not that practical because you could have just used
select *
from tst
where id between 1 and 4
order by id;

But your 'search' function have have much more complex logic inside it. You
could be searching multiple columns in the database, but the application SQL
remains the same.

Cheers,
Mark

----- Original Message -----
From: "Hadley Willan" <hadley.willan@deeper.co.nz>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, October 23, 2002 12:27 PM
Subject: [GENERAL] Return a "results set", use a temporary table or use a
table?

Hadley Willan <hadley.willan@deeper.co.nz> writes:

However, my question is, what is the best way I can use to operate on
the results that are generated by C. What I mean is A or B will call
into C and, C will generate a "results set" that A or B need to use.

Try using PostgreSQL 7.3 -- it has much improved support for
set-returning functions ("table functions"), which can be defined in
PL/PgSQL, C, and SQL.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC