Thread: plpgsql question

  I am trying to calculate a value from a current record in a query and
can't seem to get it working.

Here is the shortened query;
SELECT
       s.id,
       r.the_date_time,
       s.open_price,
       s.high_price,
       s.low_price,
       s.close_price,
       thesheet_onepair.symbol,
       r.buy_long,
       r.buy_stop,
       r.sell_cl,
       r.stop_sell,
       r.sell_short,
       r.sell_stop,
       r.buy_os,
       r.stop_buy,
       check_long_profit ()as tst
       FROM
         public.thesheet_dailystats s,
         public.thesheet_recommendation r,
         public.thesheet_onepair
       WHERE
         s.one_pair_id = thesheet_onepair.id AND
         s.the_date = r.the_date_time::date AND
         r.one_pair_id = thesheet_onepair.id
       ORDER BY
         r.the_date_time DESC,
         thesheet_onepair.id ASC;


check_long_profit is the function. I basically want to do some
calculations on this record and return some calculated fields.

I can't seem to figure out how to pass the row.

Here is my header for the function
create or replace function check_long_profit () returns integer AS $$

I know I am missing something easy.

Thanks

Hello

you used a wrong syntax

see http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Usage_PL.2FpgSQL_function_with_parametres_of_type_table

Regards

Pavel Stehule

2010/8/28 Jon Griffin <jon@jongriffin.com>:
>  I am trying to calculate a value from a current record in a query and can't
> seem to get it working.
>
> Here is the shortened query;
> SELECT
>      s.id,
>      r.the_date_time,
>      s.open_price,
>      s.high_price,
>      s.low_price,
>      s.close_price,
>      thesheet_onepair.symbol,
>      r.buy_long,
>      r.buy_stop,
>      r.sell_cl,
>      r.stop_sell,
>      r.sell_short,
>      r.sell_stop,
>      r.buy_os,
>      r.stop_buy,
>      check_long_profit ()as tst
>      FROM
>        public.thesheet_dailystats s,
>        public.thesheet_recommendation r,
>        public.thesheet_onepair
>      WHERE
>        s.one_pair_id = thesheet_onepair.id AND
>        s.the_date = r.the_date_time::date AND
>        r.one_pair_id = thesheet_onepair.id
>      ORDER BY
>        r.the_date_time DESC,
>        thesheet_onepair.id ASC;
>
>
> check_long_profit is the function. I basically want to do some calculations
> on this record and return some calculated fields.
>
> I can't seem to figure out how to pass the row.
>
> Here is my header for the function
> create or replace function check_long_profit () returns integer AS $$
>
> I know I am missing something easy.
>
> Thanks
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>