Re: 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(dot)willan(at)deeper(dot)co(dot)nz>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, October 23, 2002 12:27 PM
Subject: [GENERAL] Return a "results set", use a temporary table or use a
table?

> Hi. I've got three functions A,B and C.
> C is designed that it can be used by both A and B and recieves it's
> parameters accordingly.
>
> 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.
>
> Therefore, should C be returning that results set? (how? just point me
> to docs somewhere as I can't return a RECORD and a ROWTYPE only has a
> single row
> Or should C be creating a temporary table for A or B to use? Can
> postgres do that?
> Or should I create a table called X that I stuff the results from C
> into, operate on with A or B then have A or B delete all records from?
>
> I saw an example from someone earlier that returned a "setof", but I
> can't seem to find that in the docs or a list of valid RETURN types for
> functions?
>
> Thank You.
> Hadley
>
> --
> Hadley Willan > Systems Development > Deeper Design Limited.
> hadley(at)deeper(dot)co(dot)nz > www.deeperdesign.com > +64 (21) 28 41 463
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>