Re: FW: Re: create temp in function

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(dot)stehule(at)gmail(dot)com> 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(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.