Обсуждение: Help to realise function

Hi all,

Can anyone offer suggestions on how to realise this function....

It is the $1 as the table name that is the problem.

I'm using Version 7.4.5

create or replace function last_scan( varchar, varchar ) returns float as '
    declare
        result float;

    begin
        result := value from $1 where parameter = $2 order by dt desc limit 1;

        return result;
    end;
' LANGUAGE plpgsql;



Thanks

Allan


The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended
recipient,use, disclosure or copying of this information is prohibited. If you have received this document in error,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin this email or any attachments.
 

On Mon, 3 Apr 2006 14:57:14 +1000, "Harvey, Allan AC" <HarveyA@OneSteel.com> wrote:
> Hi all,
>
> Can anyone offer suggestions on how to realise this function....
>
> It is the $1 as the table name that is the problem.
>
> I'm using Version 7.4.5
>
> create or replace function last_scan( varchar, varchar ) returns float as '
>     declare
>         result float;
>
>     begin
>         result := value from $1 where parameter = $2 order by dt desc limit 1;
>
>         return result;
>     end;
> ' LANGUAGE plpgsql;

try executing the query

declare
    r record;
begin
    for r in execute 'select value from '||quote_ident($1)||' where
    parameter = '||quote_literal($2)||' order by dt desc limit 1' loop
        return r.value;
    end loop;
    return null;
end;

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+