Re: FW: Re: [GENERAL] create temp in function

[Kerri Reno]

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 <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.