Обсуждение: Re: ERROR: syntax error at or near "IF"... why?

On 30 Apr, 07:30, Johan Nel <johan555.nel...@xsinet555.co.za> wrote:
> Daniel,
>
> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
> >  ROLLBACK;
> > END IF
> > COMMIT;
>
> > i always get the error
> > ERROR:  syntax error at or near "IF"
>
> > Where am i mistaken?
>
> SELECT returns in essence a record or setof records.
>
> DECLARE _credit int;
> ...
> SELECT credit FROM users WHERE name = 'mary' INTO _credit;
> IF _credit < 0 THEN
>    ROLLBACK;
> END IF;
>
> If there is a chance that the select returns more than one record you
> can do something similar to:
> DECLARE rec record;
> ...
> FOR rec IN (SELECT credit FROM users WHERE name = 'mary'
> LOOP
>    IF rec.credit < 0 THEN
>      ...
>    ELSE
>      ...
>    END IF;
> END LOOP;
>
> HTH,
>
> Johan Nel
> Pretoria, South Africa.

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
-----------------------------------------------------------
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
                                           ^
-----------------------------------------------------------

For the if statement, i've tryed that
IF 2 = 2 THEN
 ROLLBACK;
END IF

but still the error near the "IF"..
dunno.. in mysql the if can be used in that way.. in postgres no?

On 30/04/2009 07:45, DaNieL..! wrote:

> I tryed the declare, before and after the BEGIN;, but allways returns
> me the error:
> -----------------------------------------------------------
> ERROR: syntax error at or near "int";
> LINE 1: DECLARE _mycredit int;
>                                            ^
> -----------------------------------------------------------

I missed what came before in this thread, but in plpgsql functions the
DECLARE comes before BEGIN:

  create or replace function my_function() returns....
  as
  $$
  declare
    ....
  begin
    ....
    return....
  end;
  $$
  language plpgsql;

Can you show us the full function code again please?

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

DaNieL..! wrote:
> I tryed the declare, before and after the BEGIN;, but allways returns
> me the error:
> -----------------------------------------------------------
> ERROR: syntax error at or near "int";
> LINE 1: DECLARE _mycredit int;
>                                            ^
> -----------------------------------------------------------
>
> For the if statement, i've tryed that
> IF 2 = 2 THEN
>  ROLLBACK;
> END IF
>
> but still the error near the "IF"..
> dunno.. in mysql the if can be used in that way.. in postgres no?

Sorry was thinking FUNCTION way of doing it.  DECLARE can only be used
in a stored procedure/function.

Johan

On Thu, Apr 30, 2009 at 1:45 AM, DaNieL..! <daniele.pignedoli@gmail.com> wrote:
>>
>> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
>> >  ROLLBACK;
>> > END IF
>> > COMMIT;
>>
>> > i always get the error
>> > ERROR:  syntax error at or near "IF"
>>

if you're inside a server-side function then you cannot use COMMIT nor
ROLLBACK; if you aren't inside a server-side function then you cannot
use IF

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157