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