Search Postgresql Archives

Re: FW: Re: create temp in function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux