Re: FW: Re: create temp in function

["Kerri Reno" <kreno@xxxxxxxxxx>]

I tried that, too, and got a different error.

NOTICE:  query: select salary_schedule, pay_column, step from saltab07 where cp_id =        32

ERROR: syntax error at or near "$2"
SQL state: 42601
Context: PL/pgSQL function "get_salary_schedule" line 15 at execute statement

In the PG log file, it says:
syntax error at or near "$2"
SELECT $1 into $2, $3, $4

My code is below

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;
    sched text;
    cl text;
    st integer;
    query text;
begin
    query = 'select ' || 'salary_schedule, pay_column, step from ' || tbl ||
        ' where cp_id = ' || to_char(tcp_id,'99999999');
    raise notice 'query: %', query;
    execute query into sched, cl, st;
    arow := (sched, cl, st);
    return arow;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

I aslo tried:
    execute query into (sched, cl, st);
which gave me  'syntax error on ('

and 
    execute query into arow;
which gave me 'record "arow" is not assigned yet'

Thanks!
Kerri

On 4/22/08, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hello



you don't need upgrade to 8.3. Just use dynamic statements. Like:

BEGIN
  EXECUTE 'CREATE TEMP TABLE a ...';
  a)
    EXECUTE 'SELECT * FROM a WHERE ...' INTO somevars;
  b)
    FOR vars IN EXECUTE 'SELECT * FROM .. ' LOOP

Regards

Pavel Stehule

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