Re: Return a "results set", use a temporary table or use a table? - Mailing list pgsql-general

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?


> 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@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>