Re: FW: Re: create temp in function

["Kerri Reno" <kreno@xxxxxxxxxx>]

Thanks for all who helped me with this.  I just upgraded our one remaining database to 8.2 and EXECUTE INTO worked great.

THANKS!
Kerri

On 4/22/08, Klint Gore <kgore4@xxxxxxxxxx> wrote:

Kerri Reno wrote:

So the reason I'm getting the error is that I'm running it in 8.0.  Thanks so much for your help!
Kerri

use FOR instead

CREATE OR REPLACE FUNCTION private.get_salary_schedule(integer, text)
 RETURNS record AS
$BODY$
declare
  tcp_id alias for $1;
  tfy alias for $2;
  tbl text := 'saltab' || tfy;
  arow record;
  query text;
begin
  query = 'select ' || 'salary_schedule, pay_column, step from ' || tbl ||
      ' where cp_id = ' || to_char(tcp_id,'99999999');
  raise notice 'query: %', query;
  for arow in execute query loop
      return arow;
  end loop;
  return null;
end;
$BODY$
 LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;

you probably need to protect tfy from sql injection too.  see quote_ident.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  Fax: 02 6773 3266
EMail: kgore4@xxxxxxxxxx

-- 
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@xxxxxxxxxx      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.